Wednesday, January 29, 2014

Connecting to a database using Domain account via SMS

I have my windows login given by my company. I use my windows authentication to login to my computer and then connect to any databases via SQL Server Management Studio(SMS) . 


As a DBA, I will need to grant a proper access a domain account (service accounts for some applications) to databases. Sometimes, they have issues connecting to databases though I give them proper access. So, I make sure that the domain account which is given access to my database is able to connect to the database.

In order to connect to database using a domain account,

First, I make a shortcut of SQL Server Management Studio on my desktop.
Second,  update the Target command with the following command. The path "c:\program files..." might be different because it depends on the location of your SQL server management studio is installed 

C:\Windows\System32\runas.exe /netonly /user:DOMAIN\ACCOUNTNAME "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Ssms.exe"



Then, double click on the shortcut and enter domain account password. 


SQL Server Management Studio will open and you will see your windows login(domain\yourlogin) but you are connected to a database as the domain account. Type sp_who2 in on the server and you can see your login as a domain account.

No comments:

Post a Comment

How to add a Database to AlwaysOn Availability Group with four different options

To add a database to an existing AlwaysOn availability group, MS has given us four options to choose from Automatic seeding Full database an...