Wednesday, 10 November 2010

Access SQL using a different domain account using SQL Windows authentication mode

Problem: Infrastructure has setup a new SQL Server 2008 R2 instance.  I can only remote desktop to the machine using the installation account.  AS SQL Server is setup to use Windows authentication I can't login using the local "Microsoft SQL Server Management Studio". 

Initial Hypothesis:  I can't connect remotely to the SQL instance, I can't login using remote desktop access with the appropriate account that has SQL access.  I need to remote desktop to the SQL Server using the remote access windows account i.e. demo\Installation and then runas the demo\sqladmin account to get access to the Management Studio.

Resolution: Run SQL Server Management Studio in the rights of the windows account that has SQL access.
Steps:
  • Remote desktop into the SQLServer using the windows account that has remote desktop access i.e. demo\Installation
  • Open you cmd prompt
  • Execute the cmd> C:\Users\Installation>runas /user:demo\sqlaccount "C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"
  • Enter the SqlAccount password
  • Management Studio opens up using the demo\SQLAdmin account

Update 6 Dec 2010 - Alternatively, hold down the shift key and right click the SQL Server Management menu, you are given the "run as .." menu option.

0 comments:

Post a comment