Security in SQL Server – Part 2

The estimated reading time for this post is 3 minutes

Securing SQL Server is not completely done in the instance, there must be secure network environment to reduce or avoid attacks to the server. To make secure the SQL Server there are some factors such as service account, communication protocol and firewall configuration.

SQL Server is a windows service process and windows platform runs it as an user mode process, then it needs a windows account to start it up and provides security privileges. Service Account can be user account or system account, Windows Server 2008 R2 platform and later provides “Virtual Service Account” which is starts with “NT Service\”. Be aware that SQL Server does not need an administrator security privileges, it only need to have privileges for listing directories and access the error log files, data files and log files. There are three built-in windows account such as “Local System”, “Network Service” and “Local Service” which none of them needs password.

Using Virtual Service Account

Virtual service account is introduced in Windows Server 2008 R2 and it does not required password management. It is created once the SQL Server service is installed successfully and administrator does not have right to remove or add any virtual service account. Virtual service account name is starts with “NT Service\”.

Encrypting the Session with SSL

SQL Server transfers data through network with Tabular Data Stream (TDS) protocol which is the standard format for SQL Server to communicate with the client application, Hackers can sniff the network by “WireShark” application and browse the transferred data through network. To protect the data in the network, SQL Server can encrypt the TDS protocol with SSL protocol. To enabling the SSL protocol, SQL Server needs either self-signed certificate or authorized certificate from certificate providers. Administrators can enable SSL protocol in SQL Server by enabling “Force Encryption” option or by “Encrypt” option in the application connection string.

Configuring a Firewall for SQL Server Access

To secure up SQL Server environment, administrators use Firewall to control the inbound and outbound connection, in SQL Server environment some port numbers must be configured properly for inbound and outbound connections, otherwise SQL Server cannot be reachable for client applications. Below SQL Server ports should not be blocked in the Firewall configuration.

  • Port TCP 1433 (Database Engine)
  • Port UDP 1434 (Browser)
  • Port TCP 2383 (Analysis Services)
  • Port TCP 4022 (Service Broker)

The following code returns all the ports that SQL Server currently using in your environment.

SELECT * FROM Sys.Endpoints WHERE Type = 4;

SQL Server browser is a windows service process to provide the port number of named instance of SQL Server and managing the dynamic port numbers. Disabling SQL Server Browser might cause user connection failure.

Transparent Database Encryption

Transparent Database Encryption (TDE) feature is introduced in SQL Server 2008, TDE feature encrypts whole database by the provided key and it is able to encrypt the data and log file at same time. TDE is totally transparent from user and application perspective. TDE can support encryption keys such as “Tripple_DES”, “AES_128”, “AES_192” and “AES_256”. Only “Enterprise”, “Developer” and “Data Center” editions of SQL Server have this unique feature.

Stopping un-used services can improve the SQL Server’s overall performance and reduce the security risk, and administrators are able to stop un-used SQL Server services such as “Integration Services”, “Analysis Services” or “Reporting Services”. And administrators can safely disable below services especially when there is a dedicated server for SQL Server environment.

  • DHCP Client
  • DNS Client
  • Network Location Awareness
  • Print Spooler
  • Windows Error Reporting Service
  • Windows Firewall (If there is a network firewall)
  • Shell Hardware Detection

Securing Linked-Server

Linked-Server is another feature of SQL Server which can connect the SQL Server instance to other SQL Server instance or Oracle Database to transfer data. Linked-Server has some security options as the following table.

Option Description
Not be made The access is limited to logins explicitly defined in the mapping list.
Be made without using a security context Linked server will apply the guest user permission on database.
Be made using the login’s current security context Applies impersonation for all not mapped logins, same as impersonation check-box
Be made using this security context Allows all non-mapped logins to connect using specified distant SQL Server login.

First actions that administrators usually take is to disable “XP_CMDShell”, “OLE Automation Procedure”, “OpenRowSet” and “OpenDataSource” features in SQL Server to reduce the security risk of SQL Server attacks. Endpoint is a kind of gate for SQL Server to communicate with users and applications, Endpoint can be created and used by either user or system. The protocol of the endpoint can be HTTP or TCP in SQL Server 2005 and SQL Server 2008 – R2 and only TCP in SQL Server 2012. User-defined endpoint can be for TSQL, Service Broker or Database Mirroring.

 

Hamid J. Fard

I am SQL Server Data Platform Expert with more than 9 years’ of professional experience, I am currently Microsoft Certified Master: SQL Server 2008, Microsoft Certified Solutions Master: Charter-Data Platform, Microsoft Data Platform MVP and CIW Database Design Specialist. I also do Pig, Hive and Sqoop development regarding Hadoop Big Data platform. After a few years of being a production database administrator I jumped into the role of Data Platform Expert. Being a consultant allows me to work directly with customers to help solve questions regarding database issues for SQL Server.

More Posts

Follow Me:
FacebookLinkedIn


Leave a Comment

avatar
1000
  Subscribe  
Notify of