2011/05/06

Tips-如何使用 Windows 驗證連接 SQL Server

    SQL Server 2005/2008 之後,因為安全性增強的關係,所以已經不允許使用控制台中[儲存的帳號/密碼]直接透通。

那該如何做呢?

我們可以使用 RunAs.exe 這個指令啟動 SQL Server Management Studio

    RunAs.exe 指令語法是這樣子的,詳細可以至微軟網站查詢(http://technet.microsoft.com/en-us/library/bb490994.aspx)

Syntax

runas [{/profile|/noprofile}] [/env] [/netonly] [/smartcard] [/showtrustlevels] [/trustlevel] /user:UserAccountName program

Parameters

/profile : Loads the user's profile. /profile is the default.

/no profile : Specifies that the user's profile is not to be loaded. This allows the application to load more quickly, but it can also cause a malfunction in some applications.

/env : Specifies that the current network environment be used instead of the user's local environment.

/netonly : Indicates that the user information specified is for remote access only.

/smartcard : Indicates whether the credentials are to be supplied from a smartcard.

/showtrustlevels : Lists the /trustlevel options.

/trustlevel : Specifies the level of authorization at which the application is to run. Use /showtrustlevels to see the trust levels available.

/user: UserAccountName : Specifies the name of the user account under which to run the program. The user account format should be user@domain or Domain\User.

program : Specifies the program or command to run using the account specified in /user.

/? : Displays help at the command prompt.

我們可以建立一個文字檔,取名為: SSMS(michadel_mike).cmd,再填入以下內容。

這個指令的意思是,用我的身份啟動 ssms.exe ,但當遇到網路連結時,請改用 michadel\mike

    ==(x64)注意雙引號====

    runas /netonly /user:michadel\mike "C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"

    ==(x32) 注意雙引號====

    runas /netonly /user:michadel\mike "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"

    ===========

存好後,執行時,會出現底下的視窗,要求輸入密碼。

 

image

 

 

最後,提醒一點,由於此方法是使用到網路的部份,改用 michadel\mike 帳號,

所以,若有要在程式裡面存取網路磁碟機(例如:載入*.sql文件),會用 michadel\mike 存取,會造成存取被拒的狀況發生!!

 

 

參考連結:

stackoverflow:Connect to SQL Server with Management Studio using a Windows Credential while not on the domain

http://stackoverflow.com/questions/1678216/connect-to-sql-server-with-management-studio-using-a-windows-credential-while-not

沒有留言: