An application which needs access to SQL Server is deployed to end user workstations. Windows authentication has been ruled out because that would give the user the ability to connect to the database using tools other than the application itself. Of course, access would only be to stored procedures, but there isn't a business justification for the users to connect to the database using anything other than the provided application. Application roles become an option, but then if the users connect to the SQL Server using Windows authentication for any other applications, there is the connection pooling problem that's well known with application roles (though this gets remedied in SQL Server 2005 with sp_unsetapprole). As a result, the application is typically deployed using SQL Server logins. However, how do we:
- Prevent the end user from figuring out the credentials?
- Ensure the credentials are distributed to every workstation where the application is deployed?
- Update the credentials if need be, especially if any sort of security compliance is required?
The first one is a hard one to do if the user is a local administrator on his or her workstation. Through the use of debugging tools and/or a network sniffer, it is possible for a user to be able to grab the username and password being used. However, the majority of users won't go down this road especially since the use of the sniffer should be a terminable offense in most organizations and through Group Policy the ability to debug can be removed in most cases. Therefore, encrypting the credentials, say in a registry value, will keep the merely curious out and should be sufficient in all but the most secure cases. But encrypting the credentials doesn't solve issues 2 or 3. That's where Group Policy comes in. We can use it to distribute a registry key/value containing the encrypted connection string.
Group Policy is only one of several options to use here in order to distribute a registry change. A login script which imports a registry file would be sufficient in most cases, except when the user has been logged on and a change in credentials has been made. Since the user hasn't logged off and back on, the login script hasn't re-run. That means the application would fail to connect. Another option would be a software push using SMS or Altiris, but not all organizations have such tools. We could script the push but that's going to be more complex than the group policy solution. Given all that, let's take a look at how we could do this in Group Policy.
The easiest way to do this is to create an administrative template for the application which allows an administrator to set the database connection string (or an encrypted version thereof that the application knows how to decrypt). A bare bones template is the following:
--- BEGIN .adm template file ---
#if version <= 2
#if version >= 3
PART !!DBPrompt EDITTEXT
SetDBConn="Database Connection String"
Testing="This is for testing purposes."
DBPrompt="Enter Database Connection String:"
--- END .adm template file ---
As I said, this is a bare bones administrative template. We could certainly do a lot more with this, like put in the error messages if someone is using secpol.exe. If you want more information on how to build out an administrative template, check out the Microsoft documentation. I won't go into more details here.
Once this template file has been added to the Group Policy editor, we can use it to set the connection string. The Group Policy can be applied to the OU containing the client workstations and then the client workstations will add and/or update this registry setting when they pull down the Group Policy. That solves our distribution problem. But what about changing the credentials? That's actually rather simple, too. Whenever the credentials are changed, a new connection string should be generated. Then, the Group Policy is modified accordingly. If you plan this change during non-business hours (or if you have your Group Policy refresh intervals at a fairly small time frame), users shouldn't be impacted. Their machines will receive the update and when they start up the application, it'll connect with the new credentials. Therefore, administrators will be free to change the credentials based on the schedule set in the organization's security policies and procedures.