How to write or deploy Secure Transact-SQL - gotchas & tips?

I have been thiking to write this topic in this blog since last 3 months, before that I have done fair amount of work to get you sensible & helpful resource in securing your SQL Server environment. 

This was the question asked by one of the user when there was a discussion about securing the SQL Server code.

The obvious answer is yes you can deploy SQL Server in a secure fashion where all of the contents will be in safe and all the resources that points out the security are often targeted at DBAs tasked with securing already developed applications. So when we talk about Developers then you should be aware of TSQL in addition to the application body of information that discusses writing secure .NET and ASP.NET code, including .NET code that accesses SQL Server.

So what will be the first step to develop a secure TSQL?

If your data is not an important data or doesn't consists any of your business related information then you shouldn't worry about locking down the SQL instance. But you should keep in mind that if the hacker can gain into this one system then it will be a cake walk to gain access on the network! Because of this one point you should write more secure T-SQL, and it will also make it much easier to secure your application when it is deployed to production. When you talk about Security in DBA terms the following points will arise to meet:

• At least one of your development or test SQL Servers should be running with the latest service pack and SQL security patch.
• An easy way to spot common security configuration problems on your development SQL Server is to run the Microsoft Baseline Security Analyzer against it. 

If you can secure the Development and Pre-Production environment as per the Production envrionment then it will be easy to cut out the manual tasks in keeping it up as per the security policies within your organisation. The motto is the closer you get to this goal, the more confident you can be that the code you develop will function properly in a secure production environment.

What is the default temptation for any user to use a SQL Server?

Usage of account with SYSADMIN or DBO privileges, even during development! They always defer to switch to a less privileged account until just prior to deployment. The major issue with this approach is much more difficult to reverse engineer the minimum set of needed permissions than to document these as the application is being developed. So it is most important step for every DBA (Application & Development) to apply similar levels of privileges to the users as per the Production environment.

Never try or use the higher privileged account such as SA or DBO when developing a T-SQL code, thinking that you will figure out what privileges can be taken away before the application is deployed. The likely result will be the application running with a more privileged account than is necessary. Instead, develop using a minimally privileged account. As time goes by once your development starts it will be hard to address this problem and you will be wasting the time to fix the petty issues and using correct privileged account as you develop using such an account, you will incrementally grant specific permissions to EXEC certain necessary stored procedures, SELECT from certain tables, and so on. Don't forget to document the steps and comment in the code whena  complex condition is written and also document these GRANT statements so that the same minimum permissions can be easily deployed to the production environment without any guesswork. You may think it is a good story to develop for not to use high privileged account but it is a good practice that is applicable to every stage of Software Engineering, when you perform ad hoc as well as more structured testing with an account that has the exact same set of permissions and user rights that the account used in production will have.

You might ask what will be the major advantage of using a minimally privileged account during development is that you will avoid unintentionally writing code that requires dangerous or excessive rights. One of the Microsoft documents refers out a best example, suppose you needed to interact with a third-party COM component from T-SQL. One way to do this is to send a SQL batch that directly calls sp_OACreate and sp_OAMethod to manipulate the COM object. This will work fine in a development environment where your application connects to SQL Server using a sysadmin account. However, when you attempt to ready the already developed application for production deployment, you will find that this will not work with a less privileged account. To make the application work in the production environment with a non-sysadmin account, you would have to explicitly grant EXECUTE permissions on sp_OACreate. So taking this into consideration of security implication you may have that user is managed to find a way to execute 'arbitrary' or 'un-precedented' code to gain access to system using this application login and may took advantage of this permission to instantiate a COM object like Scripting.FileSystemObject on the SQL Server, thats it the security of SQL Server and even your nework is compromised!

There are many web resources available to talk about best practices and guidelines, they will be applicable only when you test & deploy within your environment. Don't assume that SQL Server will take care of all such aspects of security, it is your own job and essential task to guard against a class of vulnerability called "SQL injection"[SQL Injection attacks - don't forget to visit guidance information from Microsoft] .  Taking this simple point into your day to day life such as securing your house when you are out or in, lock your doors & windows that will help you protect yourself, and matching the same on Technical aspects of taking care from SQL injection attacks by using a multilayered defense (few from Microsoft documentation) as follows :

• Perform validation of user-supplied input (for example, enforce data types and maximum string lengths).
• Escape character sequences that may have special meaning to the database engine. In T-SQL, two of the more common strings used in injection attacks are the single quote character (') and the comment character sequence (--).
• Don't concatenate user-supplied values inline in your T-SQL statement. Use prepared statements and parameterization, instead.

This brings up the general refers of dangers in using Dynamic SQL that means a stored procedure that internally builds up a query string and executes it through the EXEC() command or sp_executesql stored procedure could also be vulnerable. It reminds me the excellent reference on web for Dynamic SQL:

The curse and blessings of dynamic SQL & Dynamic Search Conditions in T-SQL and finally  Source Code Analyzer for SQL Injection