How to: Access SQL Server Using Predetermined Credentials
A reliable way to connect to SQL Server is to pass a user name and password in the connection string. You can use a predetermined user name and password. The recommended method is to store the predetermined user name and password on the server as part of the connectionStrings configuration section and then use protected configuration to encrypt the connection string contents. For details, see to Overview of Protected Configuration. It is also recommended that you restrict access to the Web.config file using NTFS file system permissions.
Never hard-code credentials as strings into programs in your application. Anyone who can get access to the code file, even the compiled code, will be able to get at the credentials.
Always give a predetermined user name the minimal access privileges to a resource. Never use "sa" or any other administrative-level user name. Always use strong passwords.
To store credentials in the Web.config file
In the Web.config file, create a new add key in the connectionStrings element. The connectionStrings element must appear as a child of the configuration element. For details, see Configuration Sections Schema.
The following example illustrates an add key that contains a user name and password:
<configuration> <connectionStrings> <add name="NorthwindConnection" connectionString="Data Source=localhost; Initial Catalog=Northwind; User Id=ApplicationUserID; Password=#P%19!ef2" /> </connectionStrings> </configuration>
Encrypt the connection string value using protected configuration as shown in Walkthrough: Encrypting Configuration Information Using Protected Configuration.
The following example shows how you can read credentials at run time and concatenate them into a connection string:
Dim settings As ConnectionStringSettings settings = System.Configuration.ConfigurationManager.ConnectionStrings("NorthwindConnection") Dim connectionString As String = settings.ConnectionString
ConnectionStringSettings settings; settings = System.Configuration.ConfigurationManager.ConnectionStrings["NorthwindConnection"]; string connectionString = settings.ConnectionString;