Microsoft released the latest version of the SQL Server Management Studio 17.4 a few weeks back. This release brings in exciting new features and bug fixes to the SSMS IDE for SQL Developers and DBAs. In this article, we will look at a new inbuilt tool for identifying security vulnerabilities in your database. We will also understand the improvements made to the XEvent Profiler and ShowPlan features.
Check out my earlier articles to go through a bunch of SSMS Productivity Features and tips/tricks to increase your coding speed in the SSMS IDE:
- SQL Server Management Studio 2016 Productivity Enhancements
- 10 SSMS Tips and Tricks to boost your Productivity
- Increase your Coding Speed in SQL Server Management Studio
SSMS is a free independent web installer and is decoupled from the core SQL Server Engine. You can download SQL Server Management Studio (SSMS) version 17.4 and check the release notes here
SQL Vulnerability Assessment
Databases hold most of the sensitive data and are susceptible to security threats and attacks. With data frauds and cyber attacks on a rise, it is essential that you safeguard your data by implementing resilient security features. Microsoft has been committed towards their effort to build more secure products so that their customers are protected.
With the release of SQL Server Management Studio 17.4, there is a new inbuilt tool called SQL Vulnerability Assessment that will help SQL Developers/DBAs to scan their database for security vulnerabilities.
This tool will basically run a set of predefined rules to identify potential vulnerabilities and deviations from Microsoft recommended best practices – primarily around permissions, configurations, exposure of sensitive data and more.
Once you have downloaded and installed SSMS 17.4, you can right click on your database, navigate to Tasks, select Vulnerability Assessment and click on Scan for Vulnerabilities.
You can specify the location in your local machine where you want to save the scan results. By default, it stores it in the below path:
\\Documents\SQL Server Management Studio\Vulnerability Assessment Reports
This tool is kind of a static code analysis tool like SQLCop or SonarQube which triggers a set of rules against your codebase to identify anti-patterns. I executed this tool against few of our bigger databases at work, and it took just few seconds to run and generate the report. It ran some simple read-only SQL queries against the database instance without making any changes to either the data or schema.
Once the scan is complete, the report is promptly displayed in the SSMS tab in a dashboard format.
It shows the current security state of your database, the number of rules executed, details of the success/failed checks, issue details, and remediation steps.
One of the rules which I liked was about following the principle of least privilege when granting permissions to database users – granting the minimum permissions necessary to a user or role to accomplish a given task. I found a lot of outliers to this security rule in my existing databases and went ahead and addressed the concerns.
Another cool feature about this tool is that you can customize the ruleset based on different databases by approving the selected rules as a baseline. Keep in mind that these are set of Microsoft recommended security rules and at this point, there is no provision to create your personalized new rules – especially because it is around database security. I did not notice any performance issue while running this assessment.
This new feature of scanning database for security issues and displaying the current security state in a dashboard format is a great add-on to SSMS.
Improvements to XEvent Profiler
With SQL Server Management Studio v17.3, Microsoft introduced a new profiling tool called as ‘XEvent Profiler’ which will allow you to capture trace information from right inside the SSMS IDE. This is supposed to replace SQL profiler – which has been deprecated already. There has been a lot of excitement in the SQL Community about this new tool since it is built on top of Extended Events technology and directly integrated into SSMS.
In SQL Server Management Studio v17.4, it is encouraging to see that there are further improvements done to the XEvent Profiler.
XEProfiler has been renamed to XEvent Profiler, which spells out the technology it is built on top of – Extended Events.
You can also search inside the Live Viewer window containing the extended event details. It is as simple as using the shortcut CTRL+F to search for the required search parameter.
Using the Find Next button you can browse through all the search results for your input string.
While working with SSMS 17.3, I noticed that when you add additional columns to the Live Viewer such as database_name, it does not display the name of the database in the results pane. It shows it as NULL.
I reported this issue to the Microsoft Product Team – Connect 3142981
Looking at the release notes of SSMS v17.4, there is a mention of the above Connect Issue item:
“Added database_name and client_hostname actions to appropriate events in XEvent Profiler sessions. For the change to take effect, you may need to delete existing QuickSessionStandard or QuickSessionTSQL session instances on the servers – Connect 3142981”
I installed SSMS 17.4 but still see the same issue. Please see below a screenshot of the Live Viewer which has the database_id populated correctly but not database_name.
I have reported the issue to the Microsoft Product Team and hopefully it will be addressed soon. If you are facing the same issue, I would request you to up vote the item in Connect site
Improvements to Showplan
There has been a number of improvements done to the Execution Plan window during the recent releases of SQL Server Management Studio
- compare Showplan feature allows side by side comparison of two execution plans
- ability to increase/decrease the content of the execution plans
- ability to Search in large execution plans
Analyzing a large complex SQL execution plan is challenging. Currently, you might be using different tools to help analyze execution plans, identify expensive operators and fine tune the queries.
Automating the execution plan analysis makes troubleshooting performance issues so much easier. In an earlier release of SSMS, Microsoft took the first step to automate this process of analyzing complex execution plans and identifying few common scenarios where execution plan may display inaccurate cardinality estimation.
In SSMS 17.4, you will have the ability to perform a single plan analysis directly from actual execution plan produced. There is no need to save the. sqlplan file to perform the plan analysis for a particular query.
Right-click on the execution plan and you will see a new option called as Analyze Actual Execution Plan
This will open a new panel called Showplan Analysis and will display potential issues present in the plan. For now, this feature identifies queries having Inaccurate Cardinality Estimation. It will display the operators which have a considerable difference between Actual and Estimated rows.
The right-hand side of the panel has a section called as Finding Details and contains information around this discrepancy and recommendations to potentially improve the query performance.
Finally, all the operator icons have been revamped to provide a superior look and feel
Conclusion
Data Security and Integrity are critical aspects of an organization. Tools like SQL Vulnerability Assessment are an easy way to make developers/DBAs aware of the best practices for SQL security and take one step forward in making their database more resilient towards database breaches. Microsoft is continuously investing in making developers lives easier within SSMS IDE by improving the diagnostics toolset and integrating new capabilities like XEvent Profiler, Performance Dashboard Reports, Showplan improvements, Import Flat File Wizard and more.
Samir is a frequent speaker at conferences such as PASS Summit, IT/Dec Connections, CodeStock, SQL Saturdays and CodeCamps. He is the Co-Chapter Lead of the Steel City SQL Server UserGroup, Birmingham, AL. He is the author of www.dotnetvibes.com
View all posts by Samir Behara
- What’s new in SQL Server Management Studio 17.4; SQL Vulnerability assessment and more - December 26, 2017
- Review of SQL Cop for SQL unit testing - September 29, 2017
- Querying Microsoft SQL Server 2012/2014 – Preparing for Exam 70-461 - September 8, 2017