CODEWITHSUNDEEP
sql-serversecurity
DermFrench
DermFrench

Reputation: 4057

SQL Server Application Role

I'm thinking of using application roles in SQL Server I've read the following on the Microsoft MSDN site: http://msdn.microsoft.com/en-us/library/ms190998.aspx

Connecting with an Application Role The following steps make up the process by which an application role switches security contexts:

A user executes a client application.

The client application connects to an instance of SQL Server as the user.

The application then executes the sp_setapprole stored procedure with a password known only to the application.

If the application role name and password are valid, the application role is enabled.

At this point the connection loses the permissions of the user and assumes the permissions of the application role.

I'm wondering, if the application must know the password, how best to achieve this. I would assume storing the password in source code is a security risk. Is there another secure way to deploy the password with the application (note this is a windows client application that will be deployed to user machines).

Upvotes: 4

Views: 2696

Answers (2)

DeChrist
DeChrist

Reputation: 530

There is actually another way to deploy the password with the application.

You can store the password as a secret in the database itself.

For instance, use a stored procedure or a scalar function which returns this "secret". This is an additional step in the logic you describe, to be executed just after the connection is made by the application with the user credentials.

This is possible because the users will have access to the database using Windows Authentication anyway. The permissions need to be setup so that users are granted access to connect to the database and to the programmable object only.

To "obfuscate" (NOT secure) the password, you can store an encrypted version in the database and use a simple encryption / decryption (like this one).

This approach has the following advantages:

  • The password is not stored in clear text anywhere (please note though that it will travel in clear text on the network if you do not use SSL Encryption)
  • Users of the application are not required to provide any input
  • The application source code does not include the password
  • The application deployment does not include the password
  • The password can be reset very easily, for instance on a schedule

Upvotes: 2

Remus Rusanu
Remus Rusanu

Reputation: 294187

There is no way to deploy a password on a user workstation w/o a local administrator being able to discover it. You can only raise the bar so high, but if the price is worth it they will find it.

You should rely on the user providing the password, which ultimately boils down to using Windows authentication instead, if possible. You should always assume that whatever privileges the application has, the user has them as well and he/she can exercise them using an alternative access API (ie. any query tool). If you cannot trust the user with certain privileges, then you must not deploy the application on his/her computer. For example use a multi-tier solution that isolates the database from the user and add any necessary validation in this intermediate tier (most ASP.Net and/or WCF apps would qualify as such multi-tier when done properly).

Upvotes: 0

Related Questions