The SQL Dude!

On-Disk & In-Memory Pages that teach you the SQL way of life!

Archive for the ‘Security’ Category

Checklist for SQL Server Database Audit & Security Hardening

Posted by Sudarshan Narasimhan on June 8, 2016


TheAuditIsComing
I’ve been involved in a lot of IT security audits, many times due to legislative requirements. A few good examples of these are PCI, SOX, HIPAA, GLBA audits. Each of these US legislative acts have a set of defined security standards & configurations that organizations have to adhere to.  Audits for these systems happen both internally and externally to ensure the company is compliant to these standards and any remediation’s are acted upon.

Being a DBA, you’re usually involved in these IT audits. The scope for these audits should be very well defined per your internal IT security policies. As a database administrator, you are responsible for the secure management of the company’s data, vis-à-vis compliance data. The systems in scope for these databases are usually ones (but not limited to) that contain the following data:

  1. Payment Card Data or database systems used for Payment processing (Online payment web systems, retail POS etc)
  2. Financial Data (like Bank, Insurance, Stocks etc)
  3. Health Information (like medical, patient records etc)
  4. Personally Identifiable Information (PII) data (like client/member data including DOB, Tax Number, SSN etc)

While each of these compliance regulations are very-well documented (especially PCI DSS), you must follow certain best practices or standards for any DBMS system, irrespective of the requirements. In today’s world, data leaks are becoming increasing prevalent and within the DB world, it is very essential to adhere to strict & enforceable data security practices.

All that said, coming to SQL Server security good practices, I’ve listed what I feel are the bare-minimum security checks that need to be performed. You can add stuff to this, but in my view these are essential and non-negotiable settings that you must check as a DBA. I’ll probably share some scripts & code to monitor these setting centrally in an automated way, in another post. This post is to list down and share these settings so that you are aware of the various things to consider when looking at SQL Server Security Hardening & Audits.

 

CHECKLIST: Security Audit of a SQL Server Database Instance

 

Instance Level:-
1. Server Role Membership
2. Server Permissions e.g. CONTROL SERVER, ALTER ANY DATABASE
3. Public Role Permissions (harden this as much as possible on user database, leaving aside system databases)
4. BUILTIN Admins present as a login?
5. Default Port changed?
6. Orphaned AD Users & Groups must be removed
7. [NT AUTHORITY\ANONYMOUS LOGON] must be removed.

Database Level:-
1. Database Role Membership (Elevated)
2. Database Role Membership (Standard)
3. Database Permissions e.g. DELETE, ALTER, SCHEMA
4. Guest User Disabled?
5. DBO User ownership (you can read my previous post on DBO ownership HERE (point #2) to understand why this is important)
6. No Orphaned Users should exist

Windows OS Level:-
1. Local Administrators Group Members
2. Local Security Policy Privileges (following this MSDN  doc to grant only those privileges required to run SQL Services to the SQL service accounts)
3. Guest User enabled?
4. Windows Firewall configured to allow connections via a specific Port Only (I hate systems setup to allow all incoming connections to sqlservr.exe. Duh!)
5. SQL Browser Service Disabled?

Configuration:-
1. XP_CMDSHELL Disabled?
2. Password Complexity & Lockout for SQL Logins (SQL logins inherit the account & password policies of the server on which the instance is running. Work with your IT admins to implement a stronger policy using GPO if required. Otherwise make sure CHECK_POLICY setting is turned ON for all SQL logins [including SA])
3. SA password rotated frequently (if you can disable the SA account, even better)
4. Cross database ownership chaining Disabled?

Patch Management
1. Are all the security bulletin patches for known vulnerabilities applied on your SQL Server instance. (Refer to https://technet.microsoft.com/en-us/security/bulletins.aspx and filter on your specific version & patch level to get the list of known security patches available).
2. Are all known Windows security patches applied (Depending on your company, this might be handled by another team, but it’s still a DB Server, so better be prepared if someone else isn’t doing their job properly 😉 )

Compliance:-
1. Access Control mechanisms in place for Sensitive/PII Data?
2. Access Control mechanisms in place for Regulatory Data?
3. SQL Server Audit enabled to track permissions/role/users changes to compliance data?

Penetration Tests
1. SA Login checks. Is anyone trying to login by brute-force using the SA account?
2. Main App login. Is anyone trying to login by brute-force using the application login (especially if it’s a SQL login)

 

I was almost about to add managed service accounts (MSA/GMSA) to this list, but considering SQL 2016 just released a week ago and most of you are probably running SQL 2012/2014 with either Clustering/AlwaysON, I’m not going to make it a must-do yet. (Damn you Microsoft, you promised GMSA support for SQL 2014 but didn’t follow through 😦 )

That’s all for now. I’ll continue this in another post with some sample code to help you through all these items.

Until then, stay safe and may the force be with you.

-TheSQLDude (Sudarshan)

Posted in Auditing, General Thoughts, Security | Tagged: , , , , , , | Leave a Comment »

Auditing T-SQL Script to find out who made changes to data in a table

Posted by Sudarshan Narasimhan on June 1, 2012


Having worked with multiple customers and different application where at times the data in the table has gotten changed and we need to find out when/where/who made the changes, I used the below T-SQL code to setup a trigger on the table and track the changes into an auditing table. There are obviously other ways to do this like SQL Profiler, XEvents etc., but I find using T-SQL more simpler and lesser overhead. So, sharing the script for others to use.

-- Note: This trigger tracks Updates and Deletes happening on a table. 
-- Please delete this trigger once the source has been identified and corrective actions have been taken.

-- 1. Creating the audit table to store information on Update/Delete
CREATE TABLE AuditTable
(
AuditID [int] IDENTITY(1,1) NOT NULL,
Timestamp datetime not null CONSTRAINT AuditTable_Timestamp DEFAULT (getdate()),
OperationType char(1),
OperationDate datetime DEFAULT (GetDate()), 
PrimaryKeyValue varchar(1000), 
OldColValue varchar(200), 
NewColValue varchar(200), 
UserName varchar(128),
AppName varchar(128),
ClientName varchar(128)
)
go

--2. Creating  the audit trigger
-- Replace PrimaryKeyValue with the PK Column Name
-- Replace NewColValue with the column name in the IF BLOCK
-- Replace OldColValue with the column name in the final SELECT statement
-- Replace TBLNAME with the name of your table which you want to track the changes for.

Create trigger TBLNAME_Audit on TBLNAME for update, delete
AS
declare @OperationType char(1),
@OperationDate datetime,
@NewColValue varchar(200),
@OldColValue varchar(200),
@UserName varchar(128),
@AppName varchar(128),
@ClientName varchar(128)

select @UserName = system_user
select @OperationDate = CURRENT_TIMESTAMP
select @ClientName = HOST_NAME()
select @AppName = APP_NAME()

if exists (select * from deleted)
      if exists (select * from inserted)
      begin
            select @OperationType = 'U'
            select @NewColValue = NewColValue from inserted
      end
      else
      begin
            select @OperationType = 'D'
            select @NewColValue = null
      end
      
Insert AuditTable (OperationType, OperationDate, PrimaryKeyValue, OldColValue, NewColValue, UserName, AppName, ClientName)
select @OperationType, @OperationDate, PrimaryKeyValue, OldColValue, @NewColValue, @UserName, @AppName, @ClientName
from deleted
go

--3. Query the audit table once the values in the base table has changed
select * from AuditTable

Posted in Auditing, Security, T-SQL | Tagged: , , , , , , | 5 Comments »

Setting up SSL encryption for SQL Server using certificates – Issues, tips & tricks

Posted by Sudarshan Narasimhan on April 21, 2012


I had posted quite a lengthy post on setting up SQL Server for SSL encryption back in October. This post has frequently ranked high on page views, thanks all for the overwhelming response. Since the time, I’ve got pinged so many times on this, that I really wanted to post another blog covering what I’ve learnt during this time and answers to some common problems faced when setting up SSL Encryption for SQL Server. So, here goes…

Basics

If a certificate is provided to SQL Server and for some reason it is not valid or SQL cannot find the certificate in the store, then it generates a self-signed certificate to encrypt communication between the server and the client. This method ensures that the login packet is always encrypted. Whether the data communication is also encrypted depends on both the server and the client. In order for the client to force encryption, the certificate used by the server should be signed by a trusted certificate authority.

When loading a certificate on the SQL Server machine, you have to keep in mind what the SQL startup account is.

1. If the SQL Server is running under a specific domain account, then you need to be logged in to the machine as the same domain account and when opening MMC, choose this option to load the Certificates snap-in, before doing the import. This makes sure that the certificate goes into the Personal store of the user who is also the SQL Service account.

image

2. If the SQL Server is running under any machine account like LocalSystem, NetworkService or LocalService, then you need to choose the option “Computer Account” in the above screenshot and then import the certificate. This will ensure that the certificate is placed under the Personal store of the machine account.

Needless to say for doing both of the above, the logged-in account must be an administrator on the machine.

Issues

1. SQL Server Configuration Manager (SSCM)  – How does it pull up the certificates and why doesn’t it show my valid certificate it the list?

Usually, SQL DBA’s don’t create certificates. This is usually done by someone else in your organization who will either install Certificate Services on a server and make it a trusted authority or buy a certificate from any on the certificate providers like VeriSign, GoDaddy.com etc. So when this certificate is not shown in SQL configuration manager, you are stuck. As I mentioned in the earlier blog post you can manually load the certificate by adding the thumbprint of the certificate into the SQL registry key “Certificate” in the location HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.<INSTANCENAME>\MSSQLServer\SuperSocketNetLib

I always wanted to find out how SSCM shows the certificate list and why sometimes some certificates even if valid are not shown there. SSCM uses WMI infrastructure to find out the certificates which are valid to be used by SQL Server. Now there are some additional checks done here which is not done by SQLServr.exe when actually loading the certificate on service startup.

You can use the following WMI query to see a list of certificates. This will be the same output you see in the SSCM Certificate drop-down. See example below

From SSCM drop-down certificate picker

image

  • As you can see above it listed 2 certificates on my machine.

Using the WMI Query

Save the below code as sqlcerts.vbs

strComputer = "."
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\Microsoft\SqlServer\ComputerManagement10")
Set colItems = objWMIService.ExecQuery( _
"SELECT * FROM SecurityCertificate",,48)
For Each objItem in colItems
Wscript.Echo "———————————–"
Wscript.Echo "SecurityCertificate instance"
Wscript.Echo "———————————–"
Wscript.Echo "ExpirationDate: " & objItem.ExpirationDate
Wscript.Echo "FriendlyName: " & objItem.FriendlyName
Wscript.Echo "IssuedBy: " & objItem.IssuedBy
Wscript.Echo "IssuedTo: " & objItem.IssuedTo
Wscript.Echo "Name: " & objItem.Name
Wscript.Echo "SHA: " & objItem.SHA
Wscript.Echo "StartDate: " & objItem.StartDate
Wscript.Echo "SystemStore: " & objItem.SystemStore
Next

Run this from command prompt cscript sqlcerts.vbs and you will see the output as shown below. I see the same 2 certificates shown by SSCM. So now we know that the certificates are shown by pulling the information from the WMI class SecurityCertificate.

Note:
If you are using using SQL 2005, in the above VBScript, replace ComputerManagement10 with ComputerManagement.

C:\Users\sudarn\Desktop>cscript certs.vbs
Microsoft (R) Windows Script Host Version 5.8
Copyright (C) Microsoft Corporation. All rights reserved.

———————————–
SecurityCertificate instance
———————————–
ExpirationDate: 30363701
FriendlyName: SUDARN2 (SQLCert)
IssuedBy: US, VA, Somewhere, MyOrg, MyOU, SUDARN2.org.company.com
IssuedTo: US, VA, Somewhere, MyOrg, MyOU, SUDARN2.org.company.com
Name: SUDARN2 (SQLCert)
SHA: 876753a93eb4310e4415a563219a41dda732c260
StartDate: 30216851
SystemStore: MY
———————————–
SecurityCertificate instance
———————————–
ExpirationDate: 31674726
FriendlyName: SCVMM_CERTIFICATE_KEY_CONTAINERSUDARN2.org.company.com
IssuedBy: SUDARN2.org.company.com
IssuedTo: SUDARN2.org.company.com
Name: SCVMM_CERTIFICATE_KEY_CONTAINERSUDARN2.org.company.com
SHA: 4ca85862a6ed93e329f68283c6d2ad1710e2c23f
StartDate: 30205010
SystemStore: MY

Part 2 of this question is why the SSCM doesn’t show your certificate. Here are some common reasons :-

  1. If yours is a clustered instance of SQL Server, then the CN in your certificate will contain the FQDN of your virtual server name of the instance and not the node names. SQL Configuration Manager does a direct match between the current machine name and the CN name in the certificate [i.e. certificates that match the machine name are only displayed], which will never match in case of a clustered instance. So you can ignore this and use the registry method to load the certificate.
  2. The certificate has expired.
  3. The Server Authentication property or the AT_KEYEXCHANGE property is not set.
  4. The Certificate is using Subject Alternate Names (SAN), which is not understood by SSCM.

 

2. Subject Alternate Names (SAN’s) and whether a certificate having SAN’s can be used by SQL Server.

Subject Alternative Names allow you to specify a list of host names to be protected by a single SSL certificate. This is useful when you want to buy 1 single certificate to secure multiple SQL Servers. Cheaper option I guess Smile. But anyways, certificates having SAN names are supported and SQL Server can use it for SSL encryption. If you provide the thumbprint value in registry, SQL Server will load this certificate. SQL Server doesn’t examine the SAN while choosing a certificate to load. I confess I hadn’t heard about SAN names before, so I tested this by creating a certificate using openssl.exe by having 2 alternate names. You can download openssl.exe for testing purposes from HERE.

1. Create a configuration file as follows. I called it cert_config.txt

distinguished_name = req_distinguished_name
x509_extensions = v3_req
prompt = no
[req_distinguished_name]
C = US
ST = VA
L = Somewhere
O = MyOrg
OU = MyOU
CN = SQLSERVER.SUDARN.COM
[v3_req]
keyUsage = keyEncipherment, dataEncipherment
extendedKeyUsage = serverAuth
subjectAltName = @alt_names
[alt_names]
DNS.1 = alternatename1
DNS.2 = alternatename2
IP.1 = 10.191.84.22
IP.2 = 10.191.84.23

The alternate names I have used are alternatename1 and alternatename2. Replace this with actual server names as applicable.

2. Once you install the tool, go to the ..\bin\openssl.exe location from command prompt and run the following command to create the certificate.

openssl.exe req -x509 -nodes -days 730 -newkey rsa:2048 -keyout C:\Users\sudarn\Desktop\cert.pem -out C:\Users\sudarn\Desktop\cert.pem -config C:\Users\sudarn\Desktop\cert_config.txt

3. Run the following command to create the PFX file

openssl.exe pkcs12 -export -out C:\Users\sudarn\Desktop\cert.pfx -in C:\Users\sudarn\Desktop\cert.pem -name "SUDARN2 (SQLCert)" -passout pass:mypassword

This will create a file called cert.pfx which can be used to install the certificate or imported using the MMC certificates snap-in.

Now that you a certificate with 2 alternate names you can see for yourself that SQL Server can load this certificate fine. Again you will see the SQL Config Manager doesn’t show this certificate. Most cases the main CN name will not match the machine name in case of SAN certificates and that’s why SSCM doesn’t show you the certificate.

Important
The SAN field in the SSL certificate is not examined by all SQL Server client drivers when they are validating the server certificate. Only fairly recent drivers examine the SAN when validating a certificate, like the following.

– SQL Server Native Client 10.5 (and higher)
– ADO.Net SqlClient 4.5
– JDBC 4.0

So, if the client application is using .Net 4.0, for example, and you try to rely on the SAN, the client application will not accept it. Keep this factor in mind when deciding to use SAN certificates or go with individual certificates for each machine.

3. I’ve setup SSL encryption but when I connect to my SQL Server, I get this error

Cannot connect to thematrix.CORP.COMPANY.COM
——————————
ADDITIONAL INFORMATION:

A connection was successfully established with the server, but then an error occurred during the pre-login handshake.
(provider: SSL Provider, error: 0 – The certificate’s CN name does not match the passed value.)
(Microsoft SQL Server, Error: -2146762481)

I was able to reproduce this issue quite easily. As you can see in the above error message, the reason the connection didn’t go through was because the certificate’s CN Name did not match the SQL Server name.

As documented in Books Online, there are 5 properties for a certificate that are mandatory to be use by SQL Server which are documented here http://msdn.microsoft.com/en-us/library/ms189067.aspx 

Here is the catch!

If your SQL Server machine is called “THEMATRIX” and you create a certificate called “MyServerName” and you provide the thumbprint of this certificate directly in the SQL server registry, SQL will load this certificate just fine. Surprised? Have a look at my repro.

1. I created a certificate using openssl.exe with the following properties.
CN = MyServerName
extendedKeyUsage = serverAuth
Valid From: 29 March 2012 21:27:39
Valid To:    29 March 2014 21:27:39

2. I loaded this certificate using the SHA1 hash value directly in the registry key "Certificate" in the following location,
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQLServer\SuperSocketNetLib

3. SQL Server successfully loaded this on restart of the service.
2012-04-03 02:52:57.100 Server       The certificate [Cert Hash(sha1) "A1A3DB2BD029B39FF9329E46B730CF8DF4BE2383"] was successfully loaded for encryption.

4. When I connected from SSMS, I get the same error as reported by my customer.
provider: SSL Provider, error: 0 – The certificate’s CN name does not match the passed value.) (Microsoft SQL Server, Error: -2146762481)

So now read the documentation again and it will make sense. What Books Online is telling you is that for a successful SSL encryption and connection, the subject Name in the certificate has to match the FQDN/Server name. In the above scenario, I deleted the certificate “MyServerName” and created a new certificate with the CN as “TheMatrix” and was able to connect successfully from SSMS.

4. Again with a new SSL encryption setup, you are faced with this error when connecting from SSMS

(provider: SSL Provider, error: 0 – The certificate chain was issued by an authority that is not trusted.)

This messages tells us that a server side certificate is used but the client could not validate the server side certificate. If your certificate is from a trusted authority but the client connection is failing with above error, do this

1) Go to SQL Configuration Manager
2) Go to SQL Native Client 10.0 Configuration > Right Click and Properties > Flags tab >
3) Set the property "Trust Server Certificate" to Yes
4) Restart the SQL Service

When the above property is set to True, SSL is used to encrypt the channel whilst bypassing walking the certificate chain to validate trust. If TrustServerCertificate is set to true and Encrypt is set to false, the channel is not encrypted. So you need to have "Force Protocol Encryption"=Yes and "Trust Server Certificate"=Yes.

5. The certificate thumbprint value in the registry is getting automatically changed whenever a failover of SQL Server is done

I must confess this was a strange issue to encounter and I saw this happening with my own eyes. Every time the SQL instance was failed over, even though the certificate was present on both the nodes, SQL was using a different self-signed certificate when starting up. I initially suspected this to be a cluster registry checkpoint issue, but after making sure that the registry value was correct on both the nodes, still on next failover some new thumbprint was getting loaded and this thumbprint value was also being placed in the registry.

There is a thumb-rule you can follow. When in doubt, use Process Monitor. So I did Smile. I collected a process monitor trace without any filters and saw this happening.

00:57:03.7155401 wmiprvse.exe 10752 RegQueryValue HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.A1XX02\MSSQLServer\SuperSocketNetLib\Certificate 6.0.6002.18005 SUCCESS Type: REG_SZ, Length: 82, Data: 7697654a3be60f1931ec04b37eae21af98aea1bd

00:57:03.7263934 wmiprvse.exe 10752 RegSetValue HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.A1XX02\MSSQLServer\SuperSocketNetLib\Certificate 6.0.6002.18005 SUCCESS Type: REG_SZ, Length: 82, Data: b8c0cf223b0efb4ca8ede15dd910400488a06631

From the above snippet you can see that wmiprvse.exe (WMI) is setting the value of the certificate to a new value. The thumbprint value is getting changed to a new value from what was there before. On further research, it was identified that this environment had System Center 2012 Configuration Manager installed and these SQL Server nodes were being managed by System Center. My colleague from the System Center team confirmed that there is a component there called “Certificate Manager” that frequently polls all the systems (ones with any roles installed) to check whether the certificate information there matches with what is stored in the System Center database (for consistency reasons). Obviously, since this is a clustered instance of SQL Server, we have edited the registry to provide the certificate value. System Center is not aware of this change, and it thinks that the certificate has been changed and puts in a request to generate a new self-signed certificate and updates the registry with the thumbprint of the new certificate. This was what was happening here. I am told System Center 2012 Service Pack 1 will have a fix for this problem. If you want confirmation, contact System Center MS Support.

6. You have configured SSL encryption for your SQL Server by manually entering the Thumbprint of a certificate, and now the SQL Service fails to start

There are many reasons for this, which you need to find out from the SQL Error log. One common problem that can happen when inputting the thumbprint into the registry when you Ctrl+C the value and paste it into the registry, some extra spaces/junk/invisible characters get copied over if you copy this from MMC. Have a look at the following KB article.

2010-04-16 18:56:31.48 Server      Error: 17182, Severity: 16, State: 1.
2010-04-16 18:56:31.48 Server      TDSSNIClient initialization failed with error 0xd, status code 0x38

SQL Server fails to start with error 17182 "TDSSNIClient initialization failed with error 0xd, status code 0x38" when server is configured to use SSL
http://support.microsoft.com/kb/2023869

 

7. You don’t see the option to export the Private Key when trying to export the certificate from one machine to another machine

This is definitely outside the realms of SQL Server. When you try to export the certificate you get to the screen where you have to choose the option to export the Private Key of the certificate, but the option is greyed out as shown below.

image

The reason the option to export the private key is greyed out is because the certificate was created without the option to export keys. There is an option during certificate creation for Server Authentication to “Mark Keys as exportable”, and you should choose this option. If you don’t, you can still export the certificate and install it on another node, but it won’t have the private key and without the private key SQL Server will not be able to load the certificate and hence cannot start.

Well folks, I’ve covered a lot of topics here. Believe me if you can follow these steps, I’m pretty confident you should have any issues before, during and after configuring SQL Server for SSL encryption. If you do happen to run into any issues, please drop in a comment on this post and I will respond back to this. Cheers!

Posted in Security | Tagged: , , , , , , , , | 22 Comments »

How to prevent users from accessing SQL Server from any application or any login expect your main application & its login

Posted by Sudarshan Narasimhan on April 7, 2012


I heard of these interesting requirements when working with one of my customers. If I were to break it down, there are 2 types of scenarios where we want to restrict access to SQL Server.

  1. Only the main application which uses a SQL Login should be able to connect to the SQL Server. None of the Windows Logins or any other login should be able to connect the SQL database. In order words, the SQL database should be accessed only by the Application Login and no one else.
  2. Access to SQL Server and the database should be only through a custom application and no other application including SQL Server Management Studio, SQLCMD etc. should allow users to access the database.

Solution for Scenario #1

The easiest and secure way of preventing other logins from accessing SQL Server is through Logon Triggers. You might wonder why this is an issue, when only required logins are added in SQL Server. Think of a scenario where the database is deployed on client machines and obviously the client machine means the user will be an Administrator on his own machine. A perfect example of this is SQL Server Express databases.

You can use Logon Triggers to prevent Windows logins or other logins from accessing SQL Server. Logon triggers are fired every time a new connection is established to SQL Server. Just like regular triggers we can perform a ROLLBACK which will roll back the connection if the login is not your application login.

For this example assume that your application has a login called “MyApplicationUser” and this is the only login that should have access (even the local Administrator must not be able to access SQL Server).

Here is the T-SQL code to implement such a requirement

--Step1: Create Audit Table
USE master
GO
CREATE TABLE dbo.loginAuditTable (
    id INT IDENTITY PRIMARY KEY,
    data XML,
    program_name nvarchar(128)
)
GO

--Step2: Create VIEW To read XML Audit Data in user-friendly format
use master
go
CREATE VIEW dbo.loginAuditView
AS
SELECT id
      ,data.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname') AS EventType
      ,data.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime') AS PostTime
      ,data.value('(/EVENT_INSTANCE/SPID)[1]', 'int') AS SPID
      ,data.value('(/EVENT_INSTANCE/ServerName)[1]', 'nvarchar(257)') AS ServerName
      ,data.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname') AS LoginName
      ,data.value('(/EVENT_INSTANCE/LoginType)[1]', 'sysname') AS LoginType
      ,data.value('(/EVENT_INSTANCE/ClientHost)[1]', 'sysname') AS ClientHostName
      ,data.value('(/EVENT_INSTANCE/IsPooled)[1]', 'bit') AS IsPooled
      ,program_name
      ,data.value('(/EVENT_INSTANCE/SID)[1]', 'nvarchar(85)') AS SID
FROM master.dbo.loginAuditTable
GO

--Step3: Create Logon Trigger to block all users expect SA & MyApplicationUser
IF EXISTS(
    SELECT * FROM master.sys.server_triggers
    WHERE parent_class_desc = 'SERVER' AND name = N'Allow_only_Application_Login_Trigger')
DROP TRIGGER [Allow_only_Application_Login_Trigger] ON ALL SERVER
GO
 
CREATE TRIGGER Allow_only_Application_Login_Trigger
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN

DECLARE @data XML
SET @data = EVENTDATA()
 
DECLARE @AppName sysname
       ,@LoginName sysname
       ,@LoginType sysname
       ,@LoginDomain sysname
       ,@HostName sysname
SELECT @AppName = [program_name]
FROM sys.dm_exec_sessions
WHERE session_id = @data.value('(/EVENT_INSTANCE/SPID)[1]', 'int')

SELECT @LoginName = @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname')
      --,@LoginDomain = @data.value('(/EVENT_INSTANCE/LoginDomain)[1]', 'sysname')
      ,@LoginType = @data.value('(/EVENT_INSTANCE/LoginType)[1]', 'sysname')
      ,@HostName = @data.value('(/EVENT_INSTANCE/ClientHost)[1]', 'sysname')

IF @LoginName not in ('MyApplicationUser','sa') 
    BEGIN
        ROLLBACK; --Disconnect the session
        --Log the exception to our Auditing table
        INSERT INTO master.dbo.loginAuditTable(data, program_name)
        VALUES(@data, @AppName)
    END 
END;

 

When a user who isn’t in the logon trigger exception list above tries to connect, they will get this error.

TITLE: Connect to Database Engine

——————————

Cannot connect to SERVERNAME.

——————————

ADDITIONAL INFORMATION:

Logon failed for login ‘Domain\TestUser1’ due to trigger execution.

Changed database context to ‘master’.

Changed language setting to us_english.

(Microsoft SQL Server, Error: 17892)

Every time a user who isn’t SA or MyApplicationUser tries to connect to SQL Server and failed with error given above, you will see the following information in the audit table including the login name, client machine name, application name etc.

image


Note:

Be careful when creating logon triggers as any mistake/bug in your code logic, will block you and then you wont be able to connect back in again. If you are in such a situation, then you can use the Dedicated Admin Connection (DAC) to make a connection and then disable/drop the logon trigger. Connections via the DAC don’t fire login triggers and there can be at max 1 DAC connection for an entire instance. DAC can enabled from sp_configure.

EXEC sp_configure 'remote admin connections', 1;
GO
RECONFIGURE;

As a best practice, make sure you have DAC enabled on all your SQL instances, because it is a life-saver in situations like this Smile. To make a DAC connection use the -A switch, sqlcmd -A -D master or Admin:ServerName from SSMS.

Solution for Scenario #2

Scenario #2 is a little more interesting and more complicated. Basically, the ask is to prevent all database access except from 1 specific client application. So even tools like SSMS, SQLCMD, Profiler etc. should not be able to connect to SQL Server.

We can still implement a reasonable amount of security using Logon Triggers, but it is not 100% fool-proof. With the same logon trigger code given above, we need to use the program_name from sys.dm_exec_sessions to check if this program is allowed to establish a connection to SQL Server.

--Step3: Create Logon Trigger to block all users expect SA & MyApplicationUser
IF EXISTS(
    SELECT * FROM master.sys.server_triggers
    WHERE parent_class_desc = 'SERVER' AND name = N'Allow_only_Application_Login_Trigger')
DROP TRIGGER [Allow_only_Application_Login_Trigger] ON ALL SERVER
GO
 
CREATE TRIGGER Allow_only_Application_Login_Trigger
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN

DECLARE @data XML
SET @data = EVENTDATA()
 
DECLARE @AppName sysname
       ,@LoginName sysname
       ,@LoginType sysname
       ,@HostName sysname

SELECT @AppName = [program_name]
FROM sys.dm_exec_sessions
WHERE session_id = @data.value('(/EVENT_INSTANCE/SPID)[1]', 'int')

SELECT @LoginName = @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname')
      ,@LoginType = @data.value('(/EVENT_INSTANCE/LoginType)[1]', 'sysname')
      ,@HostName = @data.value('(/EVENT_INSTANCE/ClientHost)[1]', 'sysname')

IF @AppName not in ('MyProductionApplicationName1', 'MyReportingApplicationName2', 'Microsoft SQL Server Management Studio', 'Microsoft SQL Server Management Studio - Query') 
    BEGIN
        ROLLBACK; --Disconnect the session
        
        --Log the exception to our Auditing table
        INSERT INTO master.dbo.loginAuditTable(data, program_name)
        VALUES(@data, @AppName)
    END 
END;

 

This is the output from the Auditing table to track the applications and users who tried to connect to SQL Server.

image


The catch with this method is the the program_name or application name is not really a fool-proof method, since it is very much possible for someone to provide the correct application name in the connection string and get connected to SQL Server. There is a property called ApplicationName which can be set in the connection string. So if the application name is known, anyone can write a program to provide that name in the connection string and gain access to SQL Server.



E.g.

OLEDB Connection String

Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=master;Data Source=ProdServer1;Application Name=MyProductionApplicationName1

SQL Native Client Connection String

Integrated Security=SSPI;Persist Security Info=False;User ID="";Initial Catalog=master;Data Source=ProdServer1;Application Name=MyReportingApplicationName2

If we use the [Application Name] property in the connection string, then even if we have logon triggers, it will allow the user to connect to SQL Server, since we are just matching the application name which is specified in the connection string.

A more secure approach you could use for this is called "Application Roles". When connecting from an application you assume a particular role and only that role is granted privileges required in the database. So all apps connect via this mechanism and don’t give out SQL or NT logins for any unauthorised use. You will have to call sp_setapprole in your application with a password, once the connection is established. Once this call succeeds then the connection gets the privileges of the application role and loses privileges of the actual user, which is what we want. So if someone tried to connect to the database from SSMS or SQLCMD, they will access the DB using their credentials, which won’t have the required permissions on the tables, since only the application role has the rights on the tables. This is more secure & reliable approach, but one that requires application code change and as a DBA you will have to create the application role in SQL Server.

The steps to create application roles are given in Books Online.

Posted in Security | Tagged: , , , , , , , , | 12 Comments »

How To: SQL Server Bulk Insert with Constrained Delegation (Access is Denied)

Posted by Sudarshan Narasimhan on December 30, 2011


Well folks, I’ve not been able to blog for sometime as I was caught up with work. But fear not, these past 2 months have given me enough subject material for multiple blog posts. Let me start off with one regarding setting up SQL Server for bulk inserts from a network file share when constrained delegation is setup in Active Directory.

For SQL Server folks out there, I don’t expect you to know about Delegation let alone Constrained vs. Unconstrained.

Q&A

Q: What is Delegation?
A: A client connected to an instance of SQL Server can connect to another instance of SQL Server or another machine by forwarding the credentials of an authenticated Windows user.

E.g. Domain\User1 on Client1 connects to –> SQL Server SQL1 and accesses a remote file on –> Server2, using his own credentials i.e. Domain\User1

It is the role of the SQL Server machine SQL1 to impersonate/delegate that user when communicating with Server2. For this to happen, the SQL Server has to be configured to allow delegation (in Active Directory).

Q: What is Constrained Delegation?
A: This feature allows administrators to specify exactly which services a server or a domain account can access when using an impersonated user’s security context. More on this later. If there is no such restriction configured, then it is called as unconstrained delegation, where every service for that user can perform impersonation aka delegation. It is a security best practice to perform constrained delegation as it reduces the surface area of any attacks.

Environment

Here is a brief layout of the environment that I am going to use in this post as a reference.

image

This is a basic 3 machine architecture that is very common. Here are the various parties involved:-

  1. Client Computer – Windows PC/Workstation/Application Server: machine name TRINITY1, Client account Domain\appadmin
  2. SQL Server – Windows Server 2003+ running SQL Server 2005/2008/2008 R2 standalone instance: machine name NEOSQL, SQLSvc account Domain\sqladmin
  3. File Share on another Windows Server (basically a shared folder): machine name MORPHEUS1, where user Domain\appadmin has Full Control on the shared folder.

 

Scenario/Requirement

In an ideal scenario, here is how I want things to work. When a client application runs the SQL BULK INSERT command logged in as Domain\appadmin account to SQL Server, I want the same appadmin account to access the remote file share and read the data.

1. If a user uses a SQL Server login to connect to SQL, then the SQL Service account credentials are used to access the remote file share.

2. If a user uses a Windows account, then his own account is used to access the file share and for this to work successfully, delegation has to be configured.

BULK INSERT appdb.dbo.bulktbl

FROM ‘\\morpheus1\share\data.txt’

If not configured correctly then you will get this error when running the above bulk insert command.

Msg 4861, Level 16, State 1, Line 3

Cannot bulk load because the file "\\morpheus1\share\data.txt " could not be opened. Operating system error code 5(Access is denied.).

Configuring Unconstrained Delegation

1. Configuring permissions on the shared folder on Morpheus1. As you can see the appadmin windows account has Full Control and the sqladmin account has Read/Write permission (if any sql login is going to be used, this is not mandatory).

image

2. I am assuming that the SQL Server is running under the service account Domain\sqladmin. Login to the Domain Controller with Domain Admin rights and open up Active Directory Users and Computers MMC snap-in.

a. Make sure that the following is NOT checked.

image

b. The SQL Service account needs SPN’s (Service Principal Names) to be created before it can be configured for delegation. You can use the SetSPN.exe tool that is available with Windows SDK or sysinternals toolkit to create the SPN’s. This tool is also available bundled along with Windows Server 2008.

We need to create 4 SPN’s for the account running SQL Server service as shown below. (2 with netbios names and 2 with FQDN). Note: The command below is for a standalone default instance of SQL Server running on default port 1433. Please modify as applicable to your environment.

Setspn -A MSSQLSvc/neosql thematrix\sqladmin
Setspn -A MSSQLSvc/neosql:1433 thematrix\sqladmin
Setspn -A MSSQLSvc/neosql.thematrix.sudarn.com thematrix\sqladmin
Setspn -A MSSQLSvc/neosql.thematrix.sudarn.com:1433 thematrix\sqladmin

Once done you can query the SPN’s using setspn.exe and it should list you these 4 SPN’s.

image

c. Switch to the Delegation Tab and select the radio button by Trust this computer for delegation to any service (Kerberos only). We are setting up unconstrained delegation if this option is chosen. If you do not see the Delegation tab available, then there was some issue with creating the SPN’s in step (b) listed above.

image

d. Next find the actual machine account in Active Directory for the SQL Server machine NEOSQL and set the computer account to be trusted for delegation, as we did above.

image

e. Next, we need to check the file server where the file we want to import is located, i.e. MORPHEUS1. We need to verify that this machine has the normal 2 HOST SPN’s registered.

image

This is required to use Kerberos for authentication. Delegation will not work without this, and you will receive an error otherwise. By default each machine should have 2 HOST SPN’s created for it.

 

3. On the SQL Server machine NEOSQL, open up the Local Security Policy by going to secpol.msc –> Computer Configuration\Windows Settings\Security Settings\Local Policies\User Rights Assignment, and add the sql account Domain\sqladmin to the following policies:

  • Act as part of the operating system
  • Impersonate a client after authentication

So far the configuration given above is for unconstrained delegation. We need to make sure that this setup works before we can configure constrained delegation. Once you are done till there, reboot the SQL Server machine (NEOSQL) and the file share machine (Morpheus1), so that all the changes we made in Active Directory are reflected when the machine starts up.

Testing

Make a test connection from the client machine (TRINITY1) using sqlcmd or SSMS. Open up another connection from SSMS and run the following query to find out if the connection from the client machine is using Kerberos authentication.

select b.spid, b.hostname, b.program_name, a.auth_scheme

from sys.dm_exec_connections a

inner join sys.sysprocesses b

on a.session_id = b.spid

image

The connection from the client machine should return KERBEROS. This is required for delegation to work. If it did, then you are good to run the bulk insert statement and it should work.

Configuring Constrained Delegation

So far so good. Now comes the part that is the crux of this blog post. If you were successful in getting things to work, then read on….

1. Open up Active Directory Users and Computers MMC snap-in with a Domain Admin account as before. Open up the Domain\sqladmin account and switch to the Delegation Tab.

2. Choose the option “Trust this computer for delegation to specified services only”, select “Use any authentication protocol” and click on the “Add…” button.

image

In the dialog to select the sqladmin account (or your account which runs the SQL service).

image

 

This should list the 2 SPN’s we have previously created for this account. Select both of them and click OK.

image

Click on the “Add…” button again and enter the SQL Server machine name (NEOSQL) and choose the HOST service.

image

Click on the “Add…” button again and enter the File Share machine name (MORPHEUS1) and choose the HOST Service. We are actually interested only in the CIFS and Protected Storage service. Choosing HOST will automatically choose these 2 services.

 

image

 

After adding all of these, this is how your final configuration for the sqladmin account will look like.

image

We need the SQLSvc account (Domain\sqladmin)and file share server to have CIFS service enabled for delegation because we are accessing a remote file share and it is the role of the CIFS Service (Common Internet File Share) to perform this. If we do not have CIFS (which comes when we added the HOST Service), then your account (Domain\appadmin) from the client machine (TRINITY1) will reach the file share server (MORPHEUS1) as NT AUTHORITY\ANONYMOUS LOGON and this will not have access and fail with 0x5 (Access is Denied).

I tested this by enabling auditing on the shared folder and saw this. This is a easy test to perform to check if delegation is working or not

When the bulk insert fails with access denied we will see this in the security event log of the file server (MORPHEUS1),

Log Name:      Security
Source:        Microsoft-Windows-Security-Auditing
Date:          12/20/2011 11:17:33 PM
Event ID:      4624
Task Category: Logon
Level:         Information
Keywords:      Audit Success
User:          N/A
Computer:      MORPHEUS1.THEMATRIX.sudarn.com
Description:
An account was successfully logged on.

Subject:
    Security ID:        NULL SID
    Account Name:        –
    Account Domain:        –
    Logon ID:        0x0

Logon Type:            3

New Logon:
    Security ID:        ANONYMOUS LOGON
    Account Name:        ANONYMOUS LOGON
    Account Domain:        NT AUTHORITY

That’s all folks! Keep in mind that this configuration requires a good bit of handling with Active Directory and/or policies. Please work with your Domain Administrator while making these changes. As always, stay tuned for more… Cheers and a happy new 2012 to y’all!

 

Posted in Security | Tagged: , , , , , , , , , | 34 Comments »

To SSL or not to SSL, that is the question….

Posted by Sudarshan Narasimhan on October 22, 2011


A lot of database administrators out there would have heard of SSL (Secure Sockets Layer) and the security it offers, and thus want to configure their SQL Servers to use SSL for encryption of data on the wire. Bear in mind that SSL Encryption is very different from the built-in database encryption features like TDE. SSL is used for encrypting data transmitted across the network (on the wire) between the SQL Server and a client/application. Once configured all traffic between SQL Server and a client application is encrypted which has its advantages (secure transmission) and its disadvantages (performance impact due to the encryption and extra round-trip at connection time). SSL is a powerful feature that is a given standard of most systems and will definitely feature in a list of To-Have items if any security audit or standards are enforced in your company.

In this post I am not going to talk about what the SSL/IPSec standards, but going to focus on how to get a SSL configured for your SQL Server instance. Anyone who has setup SSL for SQL Server before might have encountered a situation where the certificate from a trusted authority does not get detected in SQL Configuration Manager. I am going to elaborate how to tackle that issue as well, so read on and stay safe!


What do you need for SSL?

Certificates! You will need to purchase/provision a certificate from a certificate authority such as VeriSign or GoDaddy.com


Pre-Requisite Reading Material

Encrypting Connections to SQL Server
http://msdn.microsoft.com/en-us/library/ms189067.aspx

How to enable SSL encryption for an instance of SQL Server by using Microsoft Management Console
http://support.microsoft.com/default.aspx?scid=kb;en-us;316898

How to Enable Channel Encryption
http://blogs.msdn.com/b/sql_protocols/archive/2005/10/04/476705.aspx

SQL Server fails to start with error 17182 "TDSSNIClient initialization failed with error 0xd, status code 0x38" when server is configured to use SSL
http://support.microsoft.com/default.aspx?scid=kb;en-US;2023869

I would highly recommend you read the above mentioned articles from MSDN and only then proceed with the steps listed below. Before going further I want to highlight a few important points from the above article regarding Certificate requirements. For SQL Server to load a SSL certificate, these are the requirements.

  1. The certificate must be in either the local computer certificate store or the current user certificate store.
  2. The current system time must be after the Valid from property of the certificate and before the Valid to property of the certificate.
  3. The certificate must be meant for server authentication. This requires the Enhanced Key Usage property of the certificate to specify Server Authentication (1.3.6.1.5.5.7.3.1).
  4. The certificate must be created by using the KeySpec option of AT_KEYEXCHANGE
  5. The Subject property of the certificate must indicate that the common name (CN) is the same as the host name or fully qualified domain name (FQDN) of the server computer. If SQL Server is running on a failover cluster, the common name must match the host name or FQDN of the virtual server and the certificates must be provisioned on all nodes in the failover cluster.

Now, these are the mandatory requirements. But how do you verify these properties are satisfied before trying to load this certificate in SQL Server. Here is where the tool CERTUTIL.exe comes in very handy. (Certutil.exe comes with Windows)

I have a certificate named SUDA24322118 which I am going to check to see if the above 5 requirements are satisfied. From an elevated command prompt, run the following: certutil -v -store "my" "SUDA24322118" >certprop.txt

Open certprop.txt in Notepad and check the following values:

Validity of the certificate (Requirement #2)

image

Certificate is created for Server Authentication (Requirement #3)

image

Key Specification Property (Requirement #4)

image

Subject Name (Requirement #5)

image

The last one is the tricky one. In my case, my machine name is SUDA24322118 and hence the subject name must be the same. Once all of the above are verified, the certificate is good to be used with SQL Server.


Wild-Card Certificates

SQL Server 2008 R2 and onwards support wildcard certificates. E.g., *.ABCXYZ.com could be the CN of a certificate. This means that the SQL Server Engine (Server side components) will allow you to load a wildcard certificate for SSL. There are some caveats you need to know regarding client-side issues, so please read the accepted wildcard examples HERE.

 

Unable to see the certificate in the drop-down list box in SQL Server Configuration Manager

If you have religiously followed all the steps till now but still unable to see/pick the certificate to load in SQL Server, then you can follow the workaround given below to use the certificate. To reproduce this issue I created 2 certificates on the same lab machine SUDA24322118 which is in a WORKGROUP.

1. Create a test certificate: I used MAKECERT.exe which is available with Windows SDK. I have Win7 SDK installed and makecert.exe is available in "C:\Program Files\Microsoft SDKs\Windows\v7.1\Bin\".

makecert -r -pe -n "CN= SUDA24322118" -b 01/01/2000 -e 01/01/2036 -eku 1.3.6.1.5.5.7.3.1 -ss my -sr localMachine -sky exchange -sp "Microsoft RSA SChannel Cryptographic Provider" -sy 12

makecert -r -pe -n "CN= SUDA24322118.bplogix.com" -b 01/01/2000 -e 01/01/2036 -eku 1.3.6.1.5.5.7.3.1 -ss my -sr localMachine -sky exchange -sp "Microsoft RSA SChannel Cryptographic Provider" -sy 12

The subject name in the 1st certificate has just the hostname of the computer whereas the second one has the FQDN. Both of them are stored in the local user store called ”my”.

2. If you open you SSCM, you see that only the 1st certificate is listed and the second one is missing.

image

Surprised? Don’t be. Both of them have the same properties and satisfied all the 5 certificate requirements. You can see the certificate if you open up mmc and add the snap-in for Certificates. When asked to chose the account, select “Computer Account” for the Local computer. Both of the above certificates will be present under Personal –> Certificates.

image

How do I get SQL Server to use the certificate with FQDN? Fortunately, there is a simple workaround for this.

3. Locate the Certificate Hash Value: We make use of certutil.exe again to get the cert hash value

certutil -store "my" "SUDA24322118"

certutil -store "my" "SUDA24322118.bplogix.com"

image

You can pipe the results of the above command to a text file and copy the hash value highlighted above. Remove all the blank spaces, trailing and leading spaces. For the sample above, the final hash value would be: 278e30a6ba1748bbabd360b9b8ad1d78e9104d87

4. Add the hash to the SQL Server instance Registry: Open up the registry (regedit) and navigate to the SQL Server instance hive. For e.g. a default instance of SQL Server 2008 R2 would be HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLServer\SuperSocketNetLib\

In the above location there would be a entry called “Certificate” which would be blank by default. Paste the hash value noted in step #3 here. This associates the certificate with this hash in the local store with this instance of SQL Server. Next time, when SQL Server starts it will load this certificate.

image

5. Enable Force Encryption to “Yes” using SQL Server Configuration Manager.

image

Note: DO NOT navigate to the Certificate tab and try to make any changes there. Just ignore the certificate tab altogether since we have directly modified the registry (which is what the tool does anyway).

6. Restart the SQL Server service. Open the latest ERRORLOG and you can see that the certificate with FQDN has been loaded successfully.

2011-10-11 22:34:10.80 Server The certificate [Cert Hash(sha1) "278E30A6BA1748BBABD360B9B8AD1D78E9104D87"] was successfully loaded for encryption.

 

7. Make a test connection from Management Studio (SSMS) and verify using the value of column encrypt_option in the DMV sys.dm_exec_connections is TRUE, meaning the connection is being encrypted. For the real techies out there you can use Netmon or Wireshark to check if the network packet is actually being encrypted.

 

That’s all folks. There is more to client-side encryption using SSL which you can read about in the above mentioned MSDN articles. Stay secure & safe. I’ll be back with more SQL Server stuff…

Posted in Security | Tagged: , , , , , , , , , , | 15 Comments »