Export (0) Print
Expand All

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.

Security noteSecurity Note

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.

Security noteSecurity Note

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

  1. 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>
    
  2. Encrypt the connection string value using protected configuration as shown in Walkthrough: Encrypting Configuration Information Using Protected Configuration.

  3. In your application, read the credentials from the ConnectionStrings property of the ConfigurationManager class.

    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;
    

Community Additions

ADD
Show:
© 2014 Microsoft