Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Securing SQL Server with SQL Secure


By:   |   Last Updated: 2015-07-08   |   Comments   |   Related Tips: More > Security

Problem

SQL Server security is as important and as complex as performance tuning, but the reality is that security sometimes just gets in the way and often takes a back seat to performance related issues.  When developing, testing and rolling out new features the main focus is always functionality and performance which leads to shortcuts where security is concerned.

Even though there have been several major security breaches over the last several years, not all systems have been modified and updated to reduce the security holes.  You may know where some of these security holes exist, but often you are unaware of issues, so the holes remain which leaves systems vulnerable.  Auditing your system to identify issues can be done using various SQL Server scripts, but you need to know what to look for to find the issues.  Also, these security audits should be done on a routine schedule, since systems are not static and changes are often implemented which can impact security.  When making security changes the only time people notice is when things don't work, so having data and reports to back up the changes you have implemented is key.

Solution

There are lots of SQL Server scripts that can be used to find weak passwords, failed logins, elevated permission levels, public access and more.  The downside is that you need to identify what to look for as well as implement an auditing solution across your environment.  A better option is to use a tool like SQL Secure that does the above and a lot more.

SQL Secure was developed by Idera to allow you to audit security settings for SQL Server for just one instance or across your entire SQL Server enterprise.  SQL Secure quickly captures security settings to alert you of potential issues, both at the server and database level.  The installation and initial security snapshot for an instance of SQL Server only takes about 10 minutes, so you can begin to find security issues immediately.

Let's take a look at some of the features and how to use SQL Secure.

SQL Secure Enterprise Security Report Card 

After SQL Secure has been installed and the first security snapshot has been taken, when you launch SQL Secure you will see the Enterprise Security Report Card as shown below.  This gives you an overall view of your environment and shows high risk, medium risk, low risk and items that are not at risk in your environment.  The data in the image below is only for one instance, but if you are monitoring multiple servers this view will give you an overview of all servers that you are managing.

By the color coding and icons, we can quickly tell there are risks that should be reviewed and addressed. By clicking on an item in the right pane, you can see detail information about the security risk as shown below.

So for the first item below, we can see that the Public Role Has Permissions in databases Test, AdvenureWorks2012 and Dyna. The Public role is used to give permissions to database objects, so users that were not granted permissions can use the Public role to access these objects.  This is considered a bad practice, because it makes access to database objects wide open instead of granting access where needed. By simply collecting the data, SQL Secure quickly identified this security issue as well as other security items.

Other items we can see from the below screenshot include logins with weak passwords, use of extended stored procedures and more. By clicking on an item, the Details section outlines the issue and where the vulnerability exists.  You can also click on the various section such as Login, Data Integrity, Configuration, etc. to only show items for that section.

Also on the left, we get a snapshot of the number of items for each risk level, so this gives you a quick overview of items that need to be addressed to tighten your SQL Server security.

sql secure enterprise security report card

SQL Secure Policies

Determining what and how to audit is probably the hardest part when creating your own security audit.  Idera has spent several years working and enhancing SQL Secure, so instead of trying to figure out how you are going to find security vulnerabilities, you can just select from pre-defined security items and let SQL Secure find the problems. SQL Secure has over 100 security policies that you can use to audit your system and the policies are grouped into the categories listed below.  Next to each category you can see the number of available security checks for that group.

sql secure policy settings

If we expand the Login tree, we can see more details for the Login policies.  In the screenshot below, we can see the details for the "sa Account Has Blank Password" policy. With each policy you can set the risk level as well as enable or disable the policy for auditing. For some of the policies there are additional criteria that can be specified to fine tune the policy.

sql secure login security checks

So the next question is which of these policies should I use.  You could use all of them, but SQL Secure has several pre-defined audit policies that you can select from as shown below.  So if you need to comply with HIPAA, PCI or other regulations you can use these predefined audit settings to assist in auditing your SQL Server security configuration.  As you can see, SQL Secure makes it easy to begin the audit process with little time needed to think through how to audit and what to audit.

sql secure policy templates

In addition, by using the Audit SQL Servers feature you can further define your security policy by selecting which servers in your environment will use this policy. You have the option to create different security policies for different types of servers; such as financial, human resources, production, development, etc.

sql secure audit server selection

SQL Secure Assessments

As I mentioned, one of the frustrating parts about making security changes is that no one notices the change unless something doesn't work. So how do you get credit for tightening your security risk exposure, this is where Assessments come into play.  Assessments let you take a point in time and use this as your outline for security fixes that need to be made.  An Assessment is based on a particular policy and can be used as your audit guide.

When you create an Assessment it is in a Draft state and once you are ready to begin tackling and tracking the security changes, you can publish the Assessment and use SQL Secure to keep a Change Log of the changes that were made.  As you fix issues, you can use SQL Secure to track what was done to solve the problem, so you have an audit trail.  Once the security audit and changes are complete, you can Approve the Assessment and this will keep a permanent record (with your notes) of the audit which cannot be deleted or changed.

A great feature of Assessments is that you can do a comparison of security items between two different Assessments.  With these two different points in time, SQL Secure can do a comparison to show you what has changed between the two timeframes.  This can show you either new issues or issues that have been rectified.

In the below screenshot, we can see a comparison of two different Assessments.  The first one shows there was a High Risk warning for Weak Passwords.  This issue was then fixed in SQL Server and a new Assessment was created showing that this is no longer an issue.  By using Assessments, you can keep an eye on your progress towards building a more secure SQL Server environment as well as show others the progress that has been made.

sql secure security comparison

SQL Secure Exploring Permissions

With the Explore Permissions feature in SQL Secure, you don't have to use other tools like SSMS to get more insight.  With a snapshot, you can explore User Permissions, Role Permissions and Object Permissions.

Let's look at an example from above where the public role has access to objects in the AdventureWorks2012 database.  If we use Role Permissions, we can select the database and the role and drill down to see what objects have been granted permissions.  We can see below that the public role has SELECT permissions on the Employee table as well as access to the system views.  Also, there are two options for looking at permissions.  The view below shows permissions Assigned directly to the public role.  You can also use Effective Permissions which will show all permissions either granted directly or inherited through other roles. This further helps you tighten security, because back doors could have been created that you did not know existed.

sql secure role permissions

The User Permissions and Object Permissions work basically the same way, where you can drill into the security settings for a particular database user or to look at permission settings for specific objects in a database.

SQL Secure Reports

SQL Secure offers over 20 built-in reports that you can use to show management security risks, use for security audits or deeper insight into various security related items for your SQL Servers. These reports are great for your audits and having a permanent document that can be used to show that an audit was conducted and the changes that were made.  The available reports are listed below.

sql secure general reports
sql secure entitlement reports
sql secure vulnerability reports
sql secure comparison reports

Here is a sample report for the Login Vulnerabilities.  The reports include recommendations on how to solve the issues as well as why this is an issue.

sql secure report logins
Recommendation

Security risks are a real problem and have been making the headlines quite frequently.  SQL Server security is just another part of the risk level, but it could also be the greatest risk if someone is able to penetrate your security either externally or internally.  Once your database layer has been compromised someone could walk away with just enough data or all of your data. 

As a DBA, a network administrator or whatever your title is you have a responsibility to protect the database layer and protect the data.  You may not have the ability to protect or manage every level of the security chain, but when it comes to SQL Server there are tools that you can use to identify and tighten your security and as part of your job you need to take a proactive approach.

As I mentioned there are many scripts that you can find to identify issues, but you also need a plan of attack to figure out what to audit.  This is where SQL Secure comes into play.  Idera has already put many hours into developing SQL Secure and continues to add new functionality, so to start improving your SQL Server security download a 14 day fully functional trial of SQL Secure.  Installation is quick and within 10 minutes you can begin to see where you have security holes.

Next Steps

MSSQLTips.com Product Spotlight sponsored by Idera makers of SQL Secure.



Last Updated: 2015-07-08


get scripts

next tip button



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools