18 Aug

SQL Server data files on Azure | Configure Azure security

Before we even start discussing about this feature we must have an idea about Azure Storage accounts and how the security works in Azure.

You can access your Azure storage account linked container because you are the owner, but let’s say you want to share this access (full or Partial) for a blob/container with someone else; to do this you must configure security keys.

In Azure portal security works on two types of Keys and you can use these keys in order to fulfill your security requirements; these are:

a. Storage Access Key (Shared Storage Key)

b. Shared Access Signature Key

Storage Access Key (Shared Storage Key): This is an Admin key which provides full access on your storage account and with this key the person can do any operation on underlying container\blob.

Shared Access Signature Key: This is another key which can be used to provide granular access on the container and helps you enforce restricted permissions (read only, read\write with expiry etc.) this key is also known as SAS Key. SAS key normally looks like a URL which defines the resources you can access along with permissions and the validity of the SAS Key.

How to get these keys:

 

Storage Access Key (Shared Storage Key)

Azure portal provides the Shared Storage Key and you don’t need to use any other tool. All you need to do is login to your Azure Portal, select the Storage Account and select Manage Access Keys from the lower bottom of the portal. Refer the screenshots below:

AzureStorageAccount-sarabpreet

AzureStorageAccessKey-sarabpreet

You can easily copy the access key by using the copy link, marked in the image above.

 

Shared Access Signature Key

As of now unfortunately Azure portal doesn’t have a GUI for this, if you want to generate a SAS key you can do that either by Programming (by calling REST APIs or by using PowerShell cmdlets) or by using Freeware or third party tools. Let me show you a way to generate this key by using an awesome freeware codeplex (open source) project named “Azure Storage Explorer” which is contributed by David.

You can download the tool from here….. It’s a small 3 MB file. The new version is very intuitive and has many new features….Once the tool has been installed you must add your storage account; in order to add your account you have to provide your Storage Access Key. Refer the screenshot below:

AzureStorageExplorer-add_new_account_sarabpreet

 

Once the Storage account has been added you can easily navigate the Storage account and containers, select the container where you want to configure the SAS key and select Security from the upper tab.

StorageExplorer-container

 

It will popup a window with two Tabs: Access Level and Shared Access Signature. You can change the default behavior of your storage container or configure a SAS key by using the tabs respectively. Refer the screenshots below to get more clarity.

AzureStorageExplorer-AccessLevel  AzureStorageExplorer-Create_SAS_Key_sarabpreet

You can read more about the tool on David’s website….here

 

Thanks,
Sarabpreet Singh


17 Oct

Find Users with DBOwner Database role

Scenario: Auditors need a list of Users who have DBOwner Database role on any of the database which are hosted on Tier 1 Servers. Giving this information was Very easy for few servers where the DBCount was around 10 to 20, but we also have 8 SharePoint servers each hosting 200-350 DBs. Extracting this information on all these Servers was a pain, to ease things I created a script which can get all this information on a single execution.

This script can help you to extract the list of users from all databases who have DBOwner role. Once you execute the query you’ll get three column output for the query, three columns are: Database_Name, Role_Name, DBUser_Name. All the column names are self-explanatory.

You can download the script from this link: Find_DBOwners_from_All_Databases.sql

This is how the output will look like:

Find_DBOwner_all_Dbs

 

Note: Tested successfully on SQL Server 2012.

Enjoy!
Happy Learning 🙂

Thanks,
Sarabpreet Singh Anand
Subscribe now to get latest Tips\blog posts in your Inbox


17 Jan

Learn SQL Server User Impersonation

As discussed in earlier Login Impersonation Blog Technically SQL Server and windows both can be configured to authenticate to another SQL instance\Windows Server against the current login account, this behavior is known as Impersonation. When one login\user acts on behalf of another login\user this behavior is also known as impersonation.

Database Level

We can easily impersonate a session by explicitly mentioning a standalone EXECUTE AS statement but for doing so you should have Impersonate permissions on the other User else you’ll not be allowed to impersonate and you’ll face the similar error mentioned below:

Impersonate error:

user impersonation error

Msg 15517, Level 16, State 1, Line 1
Cannot execute as the database principal because the principal “Sarab_WriterRole” does not exist,
this type of principal cannot be impersonated, or you do not have permission.

 

Check the User Permission to Impersonate

Let’s check if the User(Sarabpreet) has the Impersonate Permission to impersonate as Sarab_WriterRole, for doing so we’ll run the below mentioned query:

select * from sys.Database_principals

no impersonation user right

If the current logged in user has impersonate permission to impersonate the principal name will appear in the result set. Since we got the error & now the result set also doesn’t show the principal Sarab_WriterRole, it is now confirmed that Sarabpreet login doesn’t have appropriate impersonate permissions.

 

Grant Impersonate Permission on User

To grant the impersonate permission you can execute the below mentioned query, in this query we are granting Impersonate permission to Sarabpreet so that the user Sarabpreet can impersonate as Sarab_WriterRole.

GRANT IMPERSONATE ON User::Sarab_WriterRole TO Sarabpreet

Since we’ve granted the permission let’s cross verify this once:

got user impersonate permissions

You can see that now the principal Sarab_WriterRole is visible to Sarabpreet login, which means now the User Sarabpreet can execute queries by impersonating as Sarab_WriterRole.

To Revoke Impersonate Permission from User

REVOKE IMPERSONATE ON User::Sarab_WriterRole TO sarabpreet

 

Hope you enjoyed the post, feel free to leave a comment. 🙂

Thanks,
Sarabpreet Singh Anand
Subscribe now to get latest Tips\blog posts in your Inbox


16 Jan

Learn SQL Server Login Impersonation

Technically SQL Server and windows both can be configured to authenticate to another SQL instance\Windows Server against the current login account, this behavior is known as Impersonation. When one login\user acts on behalf of another login\user this behavior is also known as impersonation.

Server Level

We can easily impersonate a session by explicitly mentioning a standalone EXECUTE AS statement but for doing so you should have Impersonate permissions on the other login else you’ll not be allowed to impersonate and you’ll face the similar error mentioned below:

Impersonate error:

clip_image002

Error: Msg 15406, Level 16, State 1, Line 1

Cannot execute as the server principal because the principal “sarab_SecurityAdmin” does not exist, this type of principal cannot be impersonated, or you do not have permission.

 

Check the Permission to Impersonate

Let’s check if the login (Sarabpreet) has the Impersonate Permission to impersonate as Sarab_SecurityAdmin, for doing so we’ll run the below mentioned query:

select * from sys.server_principals

If the current logged in login has impersonate permission to impersonate the principal name will appear in the result set. Since we got the error & now the result set also doesn’t show the principal Sarab_SecurityAdmin, it is now confirmed that Sarabpreet login doesn’t have appropriate impersonate permissions.

clip_image003

 

Grant Impersonate Permission on Login

To grant the impersonate permission you can execute the below mentioned query, in this query we are granting Impersonate permission to Sarabpreet so that the login Sarabpreet can impersonate as Sarab_SecurityAdmin.

GRANT IMPERSONATE ON Login::Sarab_SecurityAdmin TO Sarabpreet

Since we’ve granted the permission let’s cross verify this once:

clip_image004

You can see that now the principal Sarab_SecurityAdmin is visible to Sarabpreet login, which means now the login Sarabpreet can execute queries by impersonating as Sarab_SecurityAdmin.

 

To Revoke Impersonate Permission

REVOKE IMPERSONATE ON Login::Sarab_SecurityAdmin TO sarabpreet

For User Impersonation read: Learn SQL Server User Impersonation

 

Hope you enjoyed the post, feel free to leave a comment. 🙂

Thanks,
Sarabpreet Singh Anand
Subscribe now to get latest Tips\blog posts in your Inbox