Generally the talks I give at conferences revolve around performance and tuning. These are always the most sought after topic and I don’t disappoint the requests because they give me an opportunity to learn something new too. Last month when I visited Sri Lanka for the first ever SQLSaturday event, the response was the same. We had a fantastic Q&A discussion which involved some random questions from attendees on a variety of topics. I think every conference should have something like this – “Ask the Experts” concept. This gets everyone together and we are having a combined fun time to say the least.
One of the topic one attendee asked was around Security. “Hey Pinal, can you recollect some top 10 things we need to take care as security practices when working with SQL Server?”. It was surely a loaded question and I was not prepared to say the least. I said, “Give me a breather buddy. I can actually do a complete end-to-end session when I come next time if you are interested. (this gave me some seconds to gather my thoughts) Now that you asked, let me throw some of them for your reference.”
The session got over and the gentleman who had asked the question had sent me a great summary of the conversation during the Q&A session. I thought to post them as a blog post so that we can elaborate over them for the future. But these are some great starters for reference.
- Application Layer Security: SQL Injection is a powerful method to damage or compromise a SQL Server. SQL Injection is the result of failing to validate user input at the application layer. So please validate all the user inputs every single time.
- Be strict about what represents valid input and reject everything else.
- Use parameterized queries and avoid string concatenation to build queries.
- Do not divulge too much information to the end user. The end user may be an attacker. Let your error message not reveal the exact problem.
- Connect to the server using a least privilege account. Do not connect with System Admin privileges. I know many of you are doing this today, but please refrain henceforth.
- Secure the communication channel between Client and SQL Server. SSL encryption be mandated for client server communication if this is critical. SSL is provided through the use of certificates issued by a Certificate Authority (CA). SSL encryption ensures the packets between the client and server are encrypted and mitigates the detection of user credentials or data by a network sniffer.
- Protect the SQL Server files on the physical level. The directory structure for SQL Server should be restricted to the SQL Server service and relevant accounts.
- Protect the permissions for the registry entries for SQL Server. They should be restricted to Administrators and the SQL Server account.
- Audit Logins by default. Logins should be audited at the Windows and SQL Server level. At the least, failed login attempts should be logged as a flag to possible intrusion attempts.
Tips shared about SQL Account Management
- Make the SQL Server accounts least privileged.
- Delete or disable any unused user accounts.
- Disable the Windows guest account (this should be automatic for a server).
- Enforce strong password policies. This is normally controlled via Group Policy.
- Restrict remote logins to the server.
- Rename administrator account to stop DDOS attacks. Accounts like “sa” must be disabled/renamed at a minimum.
Seeing the mail, I thought this is a great start to talk when it comes to security and SQL Server. I thought this blog will be one of many to follow later on this topic because I am sure a number of you will also be interested in this topic. Do let me know some of the things you do in your environments so that we can share and learn from each other.
Reference: Pinal Dave (https://blog.sqlauthority.com)
