未加星标

Custom Intrusion Detection Reporting for SQL Server

字体大小 | |
[数据库(mssql) 所属分类 数据库(mssql) | 发布者 店小二03 | 时间 2018 | 作者 红领巾 ] 0人收藏点击收藏

By: Svetlana Golovko || Related Tips:More > Auditing and Compliance

Problem

Using windows Authentication with SQL Server is the recommended (more secure) authentication mode. Windows Authentication uses Kerberos security protocol, passwords are not transferred through the network and Windows Authentication offers additional password policies that are not available for SQL Server logins. Some applications still require SQL Server logins for user authentication or for application service accounts. Read more about the advantages and disadvantages of SQL Server Authentication here .

SQL Server logins could be potentially misused (for example, the password is shared, the password is saved in clear text in an application connection string, login used where it’s not permitted, etc.).

Windows authentication is more secure, but we can face some challenges with this type of the authentication too. For example, when we want to restrict connections to the SQL Servers from specific hosts or when an account and password are shared (which is not a good practice).

We can use Windows firewall rules to allow connections from specific hosts. But what if we need setup a restriction for a specific login only? How can we monitor misused login attempts (both - Windows and SQL Server) and track logon anomalies?

Solution

You can setup alogon trigger to restrict specific users connections from dedicated hosts, but in some environments, there is no restriction setup on how users connect to SQL Server. There is potentially a policy in your company that says that users can’t connect in a specific way, but these security policies may not be enforced. Security monitoring is the best way to comply with the policies in these cases.

Every SQL Server should have at least afailed logins audit enabled. SQL Servers hosting databases for the critical applications should have both - failed and successful logins audit enabled. The successful logins audit may generate a huge amount of error log records that could be difficult to process.

Read thistip about reading multiple log files using T-SQL.

In this tip we will provide you with scripts to generate a Custom Intrusion Detection Report. This report will read SQL Server error logs from multiple SQL Servers and filter them based on the list of allowed (“white-listed”) connections.

Here are some of the examples of potential security issues related to the logins misuse:

Security Control Audit Issue / Example An Application Service Account can be only used by a specific Application and nobody supposed to use this login for other activities. Non-authorized access (SQL Server login). Somebody used SQL Server login (an application service account) by logging in from non-authorized application/server. Example: Developer connected from Visual Studio using saved credentials in a database connection string. DBAs need to run queries remotely with SQL Server Management Studio (SSMS) from their Desktops or Dedicated Admin Servers A DBA ran a query with SSMS by logging in to the Production SQL Server machine. Non-authorized access (Windows Login). A Junior DBA that has access only to the Test Servers tried to connect to a Production SQL Server Instance. Prerequisites You need to have a list of SQL Servers with allowed logins and hosts. In our example we will be using a table on a Central Management Server (CMS) (we will be calling it the “White List” table). Monitored SQL Servers must have both successful andfailed logins audit enabled. CMS is configured and all SQL Servers that have to be monitored areregistered there. Note, that you can modify the provided PowerShell script and read the list of SQL Servers from another source (for example, text file). CMS (or another SQL Server that will be running the PowerShell Script) needs to have the PowerShell version 4.0 or later (required to append records to a CSV file). This is not required if you are not going to use a PowerShell script (if you are going to use only T-SQL Script to run on a single SQL Server). We assume that there are no SQL Server tools (for example,SSMS) installed on non-database Application Servers and users don't run SQL queries on these servers. Current monitoring doesn't have filter by an application. So, we assume that any user connecting from an Application Server is an application user/service account.

Note, that the “White List” Table below has only IP addresses. Our monitoring example uses only a list of the connections using the TCP/IP protocol. We will provide later an example proving that the Named Pipes networking protocol could be difficult to monitor which might be a reason to limit the usage of it. Keep in mind that some applications still require the Named Pipes.

Readthis tip that has a checklist with security recommendations for SQL Server configuration. Amongst with the recommendation to use Windows Authentication it has the recommendation to disable unusednetwork protocols. Read this article to find out the difference between the Named Pipes and the TCP/IP Sockets.

The “White List” Table

Create the "White-list" table in a DB_Management database on CMS:

USE DB_Management
GO
CREATE TABLE dbo.sec_AllowedConnections(
LoginID nvarchar(100) NULL,
AllowedIP varchar(20) NULL,
AllowedSQLServer nvarchar(100) NULL
) ON [PRIMARY]
GO

The columns are:

LoginID -allowed login name that will be compared to the error log's records (from a login failed or succeeded event). AllowedIP - source IP address, a machine that is "white-listed" (user is allowed to connect from this machine to SQL Server). When this column's value is "NULL" it means that a user can connect from any IP address (this might be applicable for DBAs, but it's better to have a list of permitted hosts). AllowedSQLServer - destination SQL Server, the server to which user connects to. When this column's value is "NULL" it means that a user can connect to any SQL Server.

We will insert our test rules for the application accounts (svcTestApp1, svcTestApp2) and DBAs (DOMAIN\TestDBA1, DOMAIN\TestDBA2) into the White List:

USE DB_Management
GO
INSERT INTO dbo.sec_AllowedConnections VALUES ('svcTestApp1','10.1.1.1','DEMOSRV1');
INSERT INTO dbo.sec_AllowedConnections VALUES ('svcTestApp1','10.1.1.2','DEMOSRV1');
INSERT INTO dbo.sec_AllowedConnections VALUES ('svcTestApp2','10.1.1.3','DEMOSRV2');
INSERT INTO dbo.sec_AllowedConnections VALUES ('DOMAIN\TestDBA1','10.1.1.4', 'DEMOSRV3');
INSERT INTO dbo.sec_AllowedConnections VALUES ('DOMAIN\TestDBA2','10.1.1.5', NULL);
INSERT INTO dbo.sec_AllowedConnections VALUES ('DOMAIN\TestDBA2','10.1.1.6', NULL);
INSER

本文数据库(mssql)相关术语:熊片数据库 mssql数据库 oracle数据库 pubmed数据库 access数据库 万方数据库

tags: SQL,Server,Servers,logins
分页:12
转载请注明
本文标题:Custom Intrusion Detection Reporting for SQL Server
本站链接:https://www.codesec.net/view/586280.html


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mssql) | 评论(0) | 阅读(39)