There’s an interesting SQL Azure lab in public preview called “SQL Azure security services”. It let’s you review security issues and Attack surface of your SQL Azure database. To give you a taste of the usefulness of this service, following is information that you’ll find for specified database(s)/server:

– A List of security issues. For e.g.: A particular User Name has read permissions on ALL  tables/views

– List of Usernames in a database along with database permission for each user

– List of Roles along with it’s members

– List of all user-created database objects

Useful? Interesting? continue reading to know more..

In this blog-post, first I would give a step by step tutorial on how to start using it and then walk you through a report that I generated for a sample database on my SQL Azure server.

This is the home page of “SQL Azure security services”: http://www.microsoft.com/en-us/sqlazurelabs/labs/sqlazuresecurityservices.aspx .To get started, you can watch a video tutorial here but if you prefer to read, let’s gets started:

First go to https://labs.web.sqlsecurity.azure.com/scanner.html ; This is the place to access SQL Azure security services. Bookmark (save) it!

Now here you’ll be asked to login using SQL Azure credentials. I am going to login as service administrator. you’ll need to login using credentials that has access to databases in a server and select permission on tables for databases that you want to inspect.

Click on next and here you would be asked to select between scanning the complete server vs scanning individual databases. I am going to scan a complete server (which has just one database)

Ideally, if you are scanning the entire server then storing the html report in an Azure storage account is the way to got but I just have one database in the server that I specified and so I know it would not take long, so I opted for HTML output to browser:

After clicking on scan, after few seconds, I got a link to access the report:

And I would open that report (it’s in HTML format) in the browser.

The first tab is “Security issues” and it would look like:

here , you can drill down a particular issue and view Description along with recommended mitigation.

So basically, what the above report is saying me is that I have a username indiawebdev (which I purposefully created for demoing) which has select permission on all tables/views in the sample database AdventureWorksLTAZ2008R2.

And it suggests me to grant minimum set of permission on various objects.

Now let’s go to the second tab “Attack Surface”

Quick note: Since I had opted to “scan complete server” earlier, I am getting the server level information like Logins, server roles, databases but if I opted for “scan individual database” then the server information would not be provided.

Any-who let’s drill-down a little bit see what information is made available to us:

For my Database AdventureWorksLTAZ2008R2, I can see a list of usernames along with login name, type, database permissions. So with the help of this information we can review all usernames and see if there are any “unwanted” users. And also it let’s us review the permission granted to each username at db level.

Also, it lists all roles along with it’s members and this information can be used to verify that a member is granted the minimum level of permission.

It also lists all user-created database objects along with schema/type, owner information.

Now from server information, we can use the information in the Logins information to find “unwanted” logins.

It also lists the server roles by Name along with it members so here we can see if members belong to desired roles.

And it also lists Databases with it’s database owner.

So that was about it on exploring the report. Check it out! Review Attack surface of your SQL Azure server and become aware of the security issues.

Thus, in this blog post, We learned about “SQL Azure security services” in SQL Azure labs. We also saw a sample report and explored few parts of it. And I hope you got an overview of what SQL Azure security services has to offer as of today.