Unattended installation of application on multiple sites by using sa password with a symmetric key, is it possible in SQL Server?

The newest ranch of Security - trust worthy features in SQL Server 2005 usage of symmetric keys has an important role. Always the document suggests to use Windows Authentication of security purpose and better control of user/security management, but when you talk about third party tools this is often ignored and you must make use of SQL Server authentication. Think about deploying multiple application installation on your client sites and managing the same with a single application sign-on password, more often the vendors will use or suggest to use SA (System Administrator) account in SQL Server!

As always I would discourage the users to not to use SA from an application and must use Windows Authentication instead of SQL Server authentication. In the situation where it is unavoidable to perform such an highly privileged activities, you should do the following: Define the minimal permissions necessary to perform the action and also define a database role that contains those permissions. SO how can you achieve that?

Simple, take advantage of ROLES within SQL Server and assign a user to that role. Going further within SQL Server 2005 you can create a stored procedure with the EXECUTE AS clause to out perform those functions without a need to SA privilege or necessity. that will perform those functions. But this may not be accepted by certain number of vendors where you 'must' have SA privileged account on SQL Server to deploy the additional application activities, bearing in mind that Windows Authentication is not always an option. Most of them would ask you to provide a storage of password for the account which can lead to security vulnerabilities on SQL Server or brute attach on your database if that password is revealed.

So how do you securely store a password for a SQL Server login account in such a way that the application that needs to use that password can access it, but unauthorized users or applications can’t?

Encryption has got another upper-hand within SQL Server 2005 version that will help you to take a step in right direction of protecting the password with the encryption key, that is called as symmetric key. Recently I have been through these handful resources on MSDN msdn2.microsoft.com/aa480453.aspx and the recommendations in the patterns & practices links. If this is not feasible or .NET is not involved within that application design then you can make use of  CryptoAPI that can simplify key management (which becomes the issue once you’ve encrypted your password).