Category Archives: Tips

Enable SQLCMD Mode on SSMS

I really like Microsoft philosophy nowadays, they are putting a lot of time developing good open source tools like SQLCLI, SQLOps and Visual Studio Code, those tools has something in common “a built-in terminal” a multi-platform and code geek like me really appreciate those details. It is pretty neat to use the built-int terminal to do stuff without leaving your development environment. This functionality is also available on SSMS (in some way), it is called SQLCMD mode. Continue reading

SQL Vulnerability Assessment

Vulnerability assessment is probably one of the most underrated new security features in SSMS 17. This feature is not new to SQL Server though, it already exists on Azure SQL DB but it was finally introduced in SSMS with the version 17.

If you are supporting a SQL Server environment where you have to face an exhaust external or internal IT audit process every quarter or perhaps monthly basis, I think this tool is great helping you to being proactive making you aware of all the security changes or threats you must remediate before auditors arrive to your desk asking for questions.

The only requirement for this new SSMS feature, is to have the 17.4 or latest SSMS version installed on your client machine, and a valid path to store the vulnerability scan result:

According to Microsoft, the VA (let’s use the short name from now on) runs lightweight scan directly to your SQL Server instance or a specific database, looking for security vulnerabilities and deviations from best practices, such as misconfigurations, excessive permissions, and exposed sensitive data. It also checks for security best practices to be in place in order to make your database environment safe.

The VA results contains all the security checks (passed or failed) , organized in three categories: high, medium and low risk.

Report header:

Security checks detail:

If we take a look at the example above, we can see how the Vulnerability assessments provides guidance on how to resolve the failed checks, in case the security check doesn’t apply to your environment you can dismiss such security check and it will no longer appear on future checks.

Let’s dig a little bit more about the security check ID VA1287 – Sensitive data columns should be classified:

As you noticed, the screenshot has a highlighted section where the scan result shows a couple of table columns not in compliance with GDPR. As you may know GDPR is s a regulation in EU law on data protection and privacy, which goes active on 05/25; so this is one major reason why you should  consider using the VA tool.

If you scroll down to the bottom of the AV1287 check, the report provides a detailed description and the impact of having this vulnerability active in your environment:

So, at this point you have your scan results with the description and impact of each one. Now let’s take a look what the tool recommends as remediation:

As you can see there is no rocket science here, the vulnerability scan recommends to implement security measures as Always encrypted or Dynamic Data Masking (SQL Server 16 +) to protect the sensitive information in your environment, and the most important part it helps you to stay away from trouble with your internal or external IT audit.

Let’s not forget about the baseline option:
This is very useful option to avoid false positives, let’s say you determined one of the scan results does not apply in your environment and you don’t want to have it in this report. The solution is click on the “Approve as baseline”, so the next scan will show the same result marked as passed.

Thanks for reading!

Reviewing the SQL Server ERROR log


The SQL Server ERROR log is the best source to understand what’s wrong with your SQL Server instance, think this log is like your flight recorder.

In case you are not too familiar with the SQL Server ERROR log, the xp_readerrorlog extended stored procedure is your friend; and as a good friend you must get to know him. In this post, you will find some examples of how to take advantage of this undocumented stored procedure and maximize your time avoiding the utilization of slow and painful tools like Event Viewer from Windows.

First thing first, let’s understand what are the parameters we need to provide to the stored procedure and the most important how we have to use them.

Archive ID:
The first parameter is is about picking the order of the log we want to query:

  • Current log = 0
  • Archived error log #1 = 1
  • Archived error log #2 = 2

Log type:
Then, the second parameter is the SQL Server component we are focusing in:

  • SQL Server Instance Log = 1
  • SQL Agent Log = 0

So, combining these two parameter we can check the current log (0 in the first parameter) for a SQL Server instance (1 in the second parameter) or the SQL Server Agent (2 in the second parameter)

Here is how it looks like in T-SQL code:

-- Checking the current SQL Server instance and SQL Agent logs:
xp_readerrorlog 0,1 -- Remember 1 for SQL Server instance
xp_readerrorlog 0,2 -- Remember 2 fro SQL Server Agent

We also have an optional parameter we can include, it’s a simple search string. This parameter it’s really handy when you are looking for a specific event.

Here are some T-SQL examples how we can combine the first two parameters with a specific search string:

-- Checking for failed logins in our SQL Server instance:
xp_readerrorlog 0,1,'Login failed' --For SQL Server 2012 and below
xp_readerrorlog 0,1,"Login failed" --For SQL Server 2014 onwards

-- Checking for TempDB related entries in our SQL Server instance:
xp_readerrorlog 0,1,'TempDB' --For SQL Server 2012 and below
xp_readerrorlog 0,1,"TempDB" --For SQL Server 2014 onwards

Hopefully this tip could help you to save some time when looking at your SQL Server instance for recent errors or some events from the past that you might need to collect for further analysis.

Stay tuned for more DBA mastery tips.