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.
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\”.
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.
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.
The following code returns all the ports that SQL Server currently using in your environment.
SELECT * FROM Sys.Endpoints WHERE Type = 4;
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.
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.
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.
Follow Me:
Leave a Comment