SQLSailor is exploring(Part6) – Understanding firewall concepts for SQLDatabase on Windows Azure


Security is one of the most discussed topics for any cloud deployment plans and no organisation would like to compromise on security when they make that ‘final’ decision to move their database to the cloud.

In this blog post we will review how protected are our SQLDatabases within Windows Azure using firewalls.

I encountered couple of firewall related issues when I first started working with Windows Azure, and that motivated me to write this blog post.

One of the error message or a warning was

The error message was pretty much saying that the IP address (Hidden in the picture) is not configured to manage the SQLDatabase within the server.

Before we see how we can configure the IP address to grant access, we will need to review what levels of firewall rules can be set on Windows Azure.

We can set firewall rules at Server Level and Database Level

Server level rule simply means that a client within a particular IP or within a range can access all your databases within the same SQLDatabase server.

In case you would like to restrict a client with a specific IP or a range of IPs to access only specific databases within the SQLDatabase server, then the role of Database Level firewall  rules pitch in.

SQLDatabase firewall is considered very important within Windows Azure architecture. Connection attempts from the Internet and Windows Azure must first pass through the SQL Database firewall before they can reach your SQL Database server or database.

Let’s consider a simple example to learn this in a better way –

When computer A tries to connect SQLDatabase Server from web, then the originating IP address is first checked by the SQLDatabase firewall against the server level firewall and grants access if the IP is within server level range. It will check the database level firewall rule in case the IP is not found within server level firewall range and connection is granted for the specific database in case the IP falls in database level rule.

Setting up server level rules

The easiest method to create a Server level firewall is via the Windows Azure portal itself.

We will need to select the server and choose the Config option to set a range of IPs or a single IP.

We have the option to check what all IPs or IP ranges are configured at server level using view sys.firewall_rules after connecting to master database.

Setting up database level rules

The recommended approach to create a database level firewall is to use the stored procedure sp_set_database_firewall_rule

We will need to connect to the exact database for which we need to create the rule and run the stored procedure with the parameters.

[The IP mentioned above is just a test case one]

The database level IPs or range can be checked using another view sys.database_firewall_rules and we will need to connect to the database and run this view to get the accurate details.

A request with IP 10.0.0.5 will be able to access only the database for which the rule was set and not all the databases within the SQLDatabase server.

Conclusion

There is good amount of protection available on Windows Azure platform for protecting your mission critical SQLDatabases and in this post we had a quick overview of firewall level protection.

You can read more about the comprehensive security concepts for a SQLDatabase on Windows Azure here –

Windows Azure SQL Database Connection Security

Keep checking this space for more and there are lots of new and exciting stuff coming up !

This post is in continuation with my earlier posts

SQLSailor is exploring(Part1) – Creating my first SQL Database on Windows Azure

SQLSailor is exploring(Part 2) – Managing SQL Databases on Windows Azure

SQLSailor is exploring(Part3) – Designing SQL Databases on Windows Azure

SQLSailor is exploring(Part4) – Basic DML,DDL operations with SQL Databases on Windows Azure

SQLSailor is exploring(Part5) – SQLDatabase Dashboard on Windows Azure

Thanks for reading.

Advertisements

MS_DataCollectorInternalUser – A user without a login


Yesterday there was an interesting question about SQL user MS_DataCollectorInternalUser.The question was something like –

” ms_datacollectorinternaluser is showing up as a login less user.  Where did this come from? Can I remove? ”

This question gave me the idea to write a blog post about –

User without a login

My initial response to the question was – No, as it’s related to Data Collector you should not remove it.

I was interested to check more about this user. I haven’t heard about this user before, however I was very sure that it’s related to Data Collector and deleting the same will be a bad idea.

I checked more to find that the user belongs to MSDB database. I knew that we can create a user without a login, however this was the first time I was observing a user without a login for a system database.

Let’s look what is a user without a login –

Starting SQL 2005 we have the ability to create users without logins.This feature was added to replace application roles.

One of a best explanation for login less users was once quoted by Michael Hotek(B/T) in one of the forums –

” When you create a user without a login, there is no path that allows a login to be mapped to that particular user in the database.  The only way to “utilize” this particular user in the database is through impersonation.  Again this is BY DESIGN.  Login less users were added in SQL Server 2005 as a replacement to application roles.  The main reasons behind this were that you did not need to embed a password into an application and you also did not suddenly have multiple people coming into your SQL Server all with the same “login”.  By utilizing login less users, the user’s of an application login to the SQL Server using THEIR login credentials (NOT a generic login), so that you can audit activity to a given login.  Once they change context to the application database, you can issue an EXECUTE AS command to change their user context thereby giving them the authority they need within the application.”

Let’s try to create a user Test_User without a login for database

--Create a user without login  
USE [AdventureWorks]  CREATE USER test_user without login; 
go 

We will now grant db_reader access to Test_User for the database Adventureworks

--Grant db_reader access to the user without a login 
USE [AdventureWorks] 
go 
EXEC Sp_addrolemember 
  N'db_datareader', 
  N'Test_User' 
go

We will now take a login Sam which doesn’t have access to Adventureworks database at all.User Sam is trying to access a table within the Adventureworks database 

--Select statement by the user who doesn't have access to the database
USE [AdventureWorks] 
SELECT * 
FROM   humanresources.employee 
Msg 229, Level 14, State 5, Line 3
The SELECT permission was denied on the object 'Employee', database 'AdventureWorks', 
schema 'HumanResources'.

We knew that this error is obvious,and now we will impersonate user Test_User who already has access to the Adventureworks database to login Sam

This should help Sam to read the database tables isnt? Lets see

--Impersonate a user to a login
GRANT IMPERSONATE ON User::[Test_User] TO [Sam]

Once this is successful Sam should be able to Execute As user Test_User to read the tables.

--Read the database using Execute AS   
EXECUTE AS User='Test_User'
USE [AdventureWorks]
SELECT * FROM HumanResources.Employee
 

Now as we have seen what is a login without a user and how its useful from application perspective,lets look back what permissions MS_DataCollectorInternalUser is having.

This user without a login is a member of multiple databases roles within the MSDB database.


Deleting this user is not at all allowed[Deleting,modifying anything within system database is not a good idea for that matter] and it can even corrupt a Data Collector setup.

Members of the database role dc_admin can administer and use the data collector and members of the database role db_ssisoperator can administer and use SSIS. Members of SQLAgentUserRole have permissions on  local jobs and job schedules that they own.

Conclusion

Users without a login can help replacing application roles and it can be quite useful for application perspective goals.

We also reviewed the permissions for MS_DataCollectorInternalUser and came to a conclusion that altering anything would cause corruption for the Data Collector setup.

Thanks for reading.

Hiding a SQL Server Instance !


Today I accidently came across the option called ‘Hide Instance’ flag for a SQL Instance.

This property looked pretty useful for me in case I don’t want anyone to browse for my SQL Instances easily.

There are many ways by which you can browse all the SQL Instances. One way is when using SSMS to connect to an Instance.

A look up on Network Servers will show you all the servers within the same domain. In this case we will concentrate in instance SQL2012A which runs SQL Server 2012 edition.

If you set the Hide Instance option as Yes for this instance, then that will prevent the SQL Server Browser service from exposing this instance of the Database Engine to client computers.

Process 

You will need to recycle the Engine services after setting this flag as True.

Once the service is recycled this takes into effect and the instance SQL2012A will no longer will be available for browse.

Conclusion

I have never seen this flag being set as a part of security best practices; however I would really focus on this point and will check with few SQL experts if they have similar experience.

Thanks for reading.


File activation failure. The physical file name “C:\Program Files\Microsoft SQL Server\Path…” may be incorrect. The log cannot be rebuilt when the primary file is read-only.


As we have a new version of Adventureworks database for SQL Server 2012 RC 0 I wanted to start my testing on the same.I downloaded the mdf file from the Codeplex site.

As this is only the MDF file without LDF I went ahead and tried a attach(Attach Databases option) by removing the LDF file which will ensure that we will get a new LDF created automatically.

Right away I was hit hard by a strange error which said

The physical file name path looked strange to me as I did not had such a path in my server.I tried multiple ways to attach the mdf without an ldf including

EXEC sp_attach_single_file_db @dbname = 'AdventureWorks2008R2',
@physname = 'C:\SQL_Data\AdventureWorks2008R2_Data.mdf'

and also using FOR ATTACH_REBUILD_LOG method which Pinal Dave(@pinaldave) clearly explained through his blog post here

Both time same error mentioned above came yet again

File activation failure. The physical file name “C:\Program Files\Microsoft SQL Server\MSSQL11.DENALIRCO\MSSQL\DATA\AdventureWorks2008R2_log.ldf” may be incorrect.

The log cannot be rebuilt when the primary file is read-only.

Msg 1813, Level 16, State 2, Line 1

Could not open new database ‘AdventureWorks2008R2’. CREATE DATABASE is aborted.

 Solution –  I had a strong feeling that this might be really related to a permission issue and the error message is little mis-leading rather than the actual issue. I went ahead and checked the rights for the Data folder where the mdf file was placed and after re-evaluating and granting permissions to proper SQL accounts the database was attached using normal attach method.

 Conclusion – So next time when you hit this error,remember to have a look at the permissions too for the Data file folder.
Thanks for reading.

Accidentally deleted the only Login which SSMS uses to connect to Database Engine!


Yesterday in one of the SQL forums there was an issue which was reported by one of the user.The question was something like –

‘I accidentally deleted the only Login which SSMS uses to connect to Database Engine.The server doesn’t have SQL Authentication.How do I connect now? Do I need to do a Uninstall and redo the whole thing?’

Solution – The answer to this is you do not have to uninstall the whole instance and the steps to come out of this trouble is mentioned below :

Members of Windows Administrative group have sysadmin privilege in SQL Server if you start SQL Server in Single user mode.Try the below steps.

1.Add your domain login as Server Admin Group(OS level)

2. Stop SQL Server  using the command, NET STOP MSSQLSERVER if its default instance,and if its named instance,then check the below mentioned article for more syntax.

http://technet.microsoft.com/en-us/library/ms190236.aspx

3.Start SQL Server in single user using the command   NET START MSSQLSERVER /m if its default instance,and if its named instance,then check the below mentioned article for more syntax.

http://technet.microsoft.com/en-us/library/ms187598.aspx

4. Login using SSMS and your domain ID.

5. You will get an error after opening New Query as SQL will allow only single connection.

6.Disconnect and close object explorer(Important step) and click New Query again.

7. Now you are connected and you can create a login using TSQL scripts and assign sysadmin.

So next time when you find this problem,you have a proven solution available.

Do drop in a comment in-case any of the above mentioned steps is not working for you.

Thanks for reading.

Revoke all privileges from table in database


Recently I was asked this interesting question – I need to revoke all privileges from a single table in the database,how can I do that?

Solution is simple – Using TSQL you can easily achieve this task.For Select permissions you can have

REVOKE SELECT ON dbo.tablename FROM user.

You can list the permissions granted to an object using sp_helprotect ‘dbo.table’