SQL Server 2005 Connectivity, Security policies & Network protocols: simple practice to follow, after installation?

Secured by default, secured by design is the buzzword and catchy stuff for any Enterprise IT administrators. Then it comes about standards and policies to follow such as ISO , with the recent threats (last 10 years) and vulnerabilities within IT world it is most important factor you need to follow as per the compliance standards that are deemed as industry proven, Common Criteria. This 'international' standard has been ratified in the year 1999 that was designed by a group of nations to improve the availability of security-enhanced IT systems, not only standards documentation there is a process in place to evaluate your IT systems on periodic basis in terms of security.

On such terms SQL Server 2005 comes as 'trustworthy' whereby the general policy of 'off by default' or 'enable only when needed' has been implemented by design, that means connectivity-policies/services such as Service Broker, HTTP or Database Mirroring endpoints, XP_CMDSHELL on surface area are disabled by default. Also the network protocols such as TCP/IP, Named pipes are disabled too with an exception to Shared Memory is available/enabled by default. Also the VIA endpoint is disable in addition to discontinuation support for some network protocols that were available with earlier versions of SQL Server, including IPX/SPX, Appletalk, and Banyon Vines.  

Dedicated Administrator Connection (DAC) that is given secret key to DBAs to use when every other connectivity is locked out on SQL instance will also be availabile to use locally 'by default', that means you need to logon remotely to use it and anyhow you can enable it to use it remotely too. To know whether DAC is enabled or not you can refer to SQL Server 2005 detect DAC session with TSQL post here and use the feature responsibly! Be aware that DAC is not available to use in SQL Express edition unless you start that instance with a trace flag 7806.

As referred above permission to database endpoints requires CONNECT permission, as it is also disable by default that will restrict access paths and blocks some known attack vectors. It is a best practice to enable only those protocols that are needed. For example, if TCP/IP is sufficient, there is no need to enable the Named Pipes protocol. As the documentation refers to use Surface Area Configuration which is introduced in SQL Server 2005 has been taken away in upcoming 2008 version (SQL Server 2008: Administer multiple servers by designating configuration servers), so you need to get used to perform such endpoint administration using DDL statements and using SQL Server Configuration Manager that will give you a simplified user interface for enabling or disabling client protocols for a SQL Server instance. This GUI provides more granular configuration of server protocols. With Configuration Manager, you can:

·         Choose a certificate for SSL encryption.

·         Allow only encryption connections from clients.

·         Hide an instance of SQL Server from the server enumeration APIs.

·         Enable and disable TCP/IP, Shared Memory, Named Pipes, and VIA protocols.

·         Configure the name of the pipe each instance of SQL Server will use.

·         Configure a TCP/IP port number that each instance listens on for TCP/IP connections.

·         Choose whether to use TCP/IP dynamic port assignment for named instances.

So to talk about simple and better practices you need to tighten the SQL Server environment with few policies, specific recommendation for relevant needs. In summary I have implemented the following tasks in order to ensure the maximum security practices are obtained when a new platform is designed and not leaving existing platforms too:

Best practices for network connectivity

·         Limit the network protocols supported.

·         Do not enable network protocols unless they are needed.

·         Do not expose a server that is running  SQL Server to the public Internet.

·         Configure named instances of SQL Server to use specific port assignments for TCP/IP rather than dynamic ports.

·         If you must support SQL logins, install an SSL certificate from a trusted certificate authority rather than using SQL Server 2005 self-signed certificates.

·         Use "allow only encrypted connections" only if needed for end-to-end encryption of sensitive sessions.

·         Grant CONNECT permission only on endpoints to logins that need to use them. Explicitly deny CONNECT permission to endpoints that are not needed by users or groups.

By default it is better to visit the SQL Servers on Production environment with a periodical assesment of security policies and whenever a new hotfix or Service pack is released don't forget to test it thorougly to apply.