We are currently going through the exercise of converting our web applications (primarily .NET web applications) from SQL Server Authentication over to Integrated Authentication.
Integrated authentication allows for SQL Server to leverage Windows NT authentication to validate SQL Server logon accounts. This allows the user to bypass the standard SQL Server logon process. With this approach, a network user can access a SQL Server database without supplying a separate logon identification or password because SQL Server obtains the user and password information from the Windows NT network security process.
Choosing integrated authentication for ASP.NET applications is a good choice because no credentials are ever stored within your connection string for your application. Thus, security is improved. Another reason to incorporate Integrated Authentication is to facilitate account and password policy enforcement through a domain controller. For example, this might require passwords be recycled every 90 days.
“Integrated Authentication is not difficult to implement.” I’ve heard this time and time again. Actually, in an ideal scenario, this is a correct statement and the implementation requires just a few steps:
1. Set SQL Server Authentication to Mixed Mode.
2. Set identity impersonate to true and assign the username and password in the IIS.
3. Update you connection string to use one of the following variations:
a. data source=myServer;initial catalog=myDB;Integrated Security=SSPI
b. server=myServer; database=myDB;Trusted_Connection=true
Easy, right? Sure. But what if you are converting to NT Authentication and your existing network and application(s) are not ideally setup? In this case, there are a number of potential (read probable) gotchas.
There are three obstacles I wish to discuss, but today I am going to focus on one of them. This issue is the most obscure and I would bet that the majority of those tasked with estimating a conversion to NT Authentication would not even consider this condition. I’m also calling attention to this gotcha first because, in my opinion, it comes with painless and noteworthy solution.
Gotcha # 1:
Our web applications call upon a number of Windows Services. These services should be considered legacy components. They have been around for a while and they continue to do their job so they get little attention. Each of these components store connection string information in the registry.
As you have noticed, connection string formats vary based on the authentication method.
Integrated Authentication
data source=myServer;initial catalog=myDB;Integrated Security=SSPI;
SQL Server Authentication:
data source=myServer; initial catalog=myDB; user id=myUsername; password=myPassword;
In this case, the registry held the four primary SQL Server connection parameters and the service would use the associated values to build the string on the fly. Can you see where this is going? Fortunately, we identified this little issue early on and we estimated time to rework the functionality, rebuild, test and redeploy the services. This was risky since these services were rarely modified/deployed and it was going to be expensive since there were more than a dozen services which required these changes.
This was until a colleague of mine uncovered an elegant hack. [Is that an oxymoron?] He discovered that once the Integrated Security parameter of the connection string is set to SSPI parameters such as User ID and Password are virtually ignored. (He also found out that extra semi-colons don’t make a lick of difference either.)
In the end, we hacked our registry entries to allow our services to build and use NT Auth compliant connection strings.
Sample registry entries:
Resulting connection string:
datasource=myServer;integrated security=SSPI;initial catalog=myDB;user id=;password=;
This reduced risk and simplified our deployment to modifications to the registry through the execution of a single .reg script.
Opening up the services and redeploying may have been the best solution, but this hack got the job done safely and effectively and on the cheap. And we learned a bit more about how connection strings work. Who knows? This tip may help you out of a pinch someday.