I ran into Brian Knight today on the Expo floor here at TechEd.
It was good to catch up a bit with him, and I hope to do so more later
this week. While we were talking, he presented a security scenario many
of us have encountered:
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
#endif
#if version >= 3
CLASS MACHINE
CATEGORY !!TestApp
POLICY !!SetDBConn
KEYNAME "Software\Policies\MyCompany\TestApplication"
EXPLAIN !!Testing
PART !!DBPrompt EDITTEXT
VALUENAME DatabaseConnectionString
END PART
END POLICY
END CATEGORY
#endif
[strings]
TestApp="Test Application"
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.
