There are a few primary reasons why Windows Authentication should be considered a best practice. Since passwords aren’t visibly exposed in configuration files and credentials are not sent over the network, your systems tend to be more secure. Additionally, password management (expiration periods, minimum lengths and account lockout after multiple invalid logon requests) becomes a heck of a lot easier. Considering the benefits, I have no issues with applications accessing SQL Server using Integrated Security, however, I think there is a big different between application access and developer access.
Really quickly, you can setup SQL Server to run in one of two security modes: Windows Authentication or Mixed Mode. Mixed Mode is exactly as it implies and allows users to connect using Windows NT Authentication or using SQL Server Authentication. I’m a fan of Mixed Mode which allows for application accounts to reap the benefits of Integrated Security while allowing considerably easier SQL Auth access for developers, support personnel, etc.
Please consider the following scenarios:
- An instance of SQL Server is setup in the Development Environment and it isn’t running in Mixed Mode. This implies that one needs to be a member of the appropriate domain and have appropriate permissions in order to access the SQL Server. Not a big deal, right? Well, I would agree if I were a developer working onsite and the Dev Network was readily available to me. But I happen to work remotely on occasion and there isn’t an entrance point into this particular domain through VPN. Boy, SQL Authentication sure would come in handy in this case.
- Let’s say your environments (perhaps Dev, QA and Production) are hosted in separate domains. It would be painful to switch between domains in order to access each SQL Servers, wouldn’t it? (I know what you are thinking, “Why would a developer need to access QA or Production?” Special assignment. Let’s leave it at that.)
Fortunately, there’s a workaround. If you are me, you beg and plead and moan and request that security mode be changed (not that that’s easy.) OR you discover an even better solution in the appropriately named RunAs command which allows a user to run specific tools and programs with different permissions than the user’s current logon provides.
The following are a few of my favorite commands which I’ve wrapped up neatly in their own .cmd file for quick execution (you will need to update the domain and user values accordingly):
- runas /user:domain\user “C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\ssmsee.exe”
- runas /user:domain\user “C:\WINDOWS\system32\mmc.exe /s \”C:\Program Files\Microsoft SQL Server\80\Tools\BINN\SQL Server Enterprise Manager.MSC\”"
- runas /user:domain\user isqlw
This tip actually came from a wise, remote developer working in Canada who was confronted with Scenario 1 above. Pretty neat, eh?