Accessing SQL Server with Explicit Credentials
The document is archived and information here might be outdated

Accessing SQL Server with Explicit Credentials

Visual Studio .NET 2003

A reliable way to connect to SQL Server is to pass a user name and password in the connection string. You can use this strategy in different ways:

  • Prompt the user for the user name and password.
  • Use a predetermined user name and password.
    Security Note   Using a connection string to access SQL Server is not as secure as using integrated security. Use a connection string with explicit credentials only if it is impractical in your application to use integrated security.

Prompting for Credentials

You can prompt for a user name and password (for example, using Forms authentication), and then pass credentials to SQL Server in the connection string. Prompting for credentials has some disadvantages. It requires that each user be recognized separately by SQL Server. It also is inefficient for connection pooling and thus can limit scalability. For an example of Forms authentication, see Simple Forms Authentication.

Security Note   Prompting the user for a username and password in your application is a potential security threat. Credential information must be transferred from the browser to your server, and ordinary HTTP transfers are unencrypted. For best security when prompting users to provide credentials in your application, you should use secure sockets layer (SSL), which encrypts information before sending it from the browser to the server. For details on how to implement SSL, see Microsoft Knowledge Base article Q307267, "HOW TO: Secure XML Web Services with Secure Socket Layer in Windows 2000.

To pass an explicit user name in the connection string dynamically

  • After getting the user's name and password, concatenate them into the connection string in code, and then assign them to the ConnectionString property of a connection object.
    Security Note   You should be sure that the user name and password do not contain malicious content. The example below illustrates one method to do so. For more information, see Scripting Exploits.

    The following example shows a dynamically created connection string. The user name and password are assumed to be available in text boxes. The user input is filtered with a function that makes sure that it contains only pre-determined characters.

    Tip   If you are familiar with using regular expressions, you can create a more efficient version of this filter. For details, see Regular Expression Classes.
    ' Visual Basic
    Private Function ValidateInput(ByVal validchars As String, _ 
            ByVal userinput As String) As Boolean
        Dim c As Char
        For Each c In userinput
            If validchars.IndexOf(c) < 0 Then
                Return False
            End If
        Next
        Return True
    End Function
    
    Private Sub OpenDatabase()
        Dim vChars As String = _
            "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz*&%$#!"
        Dim userid As String
        Dim password As String
        If ValidateInput(vChars, UserName.Text) Then
            userid = UserName.Text
        Else
            Throw New System.Exception("Invalid user name.")
        End If
        If ValidateInput(vChars, PasswordText.Text) Then
            password = PasswordText.Text
        Else
            Throw New System.Exception("Invalid password.")
        End If
        Dim connectionString As String
        connectionString =  "data source=myserver"
        connectionString &= ";initial catalog=northwind"
        connectionString &= ";user id=" & userid
        connectionString &= ";password=" & password
        SqlConnection1.ConnectionString = connectionString
        SqlConnection1.Open()
        ' Further code to work with the database
    End Sub
    
    // C#
    private Boolean ValidateInput(String validchars, String userinput)
    {
        foreach(Char c in userinput)
        {
            if(validchars.IndexOf(c) < 0)
            {
                return false;
            }
        }
        return true;
    }
    
    private void OpenDatabase()
    {
        String vChars = 
           "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz*&%$#!";
        String userid;
        String password;
        if(ValidateInput(vChars, UserName.Text))
        {
            userid = UserName.Text;
        }
        else
        {
            throw new System.Exception("Invalid user name.");
        }
        if(ValidateInput(vChars, PasswordText.Text))
        {
            password = PasswordText.Text;
        }
        else
        {
            throw new System.Exception("Invalid password.");
        }
        String connectionString;
        connectionString =  "data source=myserver";
        connectionString += ";initial catalog=northwind";
        connectionString += ";user id=" + userid;
                connectionString += ";password=" + password;
        sqlConnection1.ConnectionString = connectionString;
        sqlConnection1.Open();
        // Further code to work with the database
    }
    

Using Predetermined Credentials

If it is not practical to prompt for credentials, you can use a predetermined user name and password. The recommended method is to store the predetermined user name and password on the server, and then read it and add it to the connection string at run time. An advantage of this technique is that your application can access the database using different credentials under different circumstances, depending on what it needs to do in the database.

Security 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 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 passwords.

You can store credentials in a variety of places. Two options are:

  • In the Web.config file. This makes it easy to deploy the configuration information along with the application. Although the configuration file is stored in the same directory as the ASP.NET application, ASP.NET prevents clients from directly accessing files with a .config extension. For additional security, you can set the configuration file's NTFS file system permissions and encrypt the connection string within the file.
  • In the Windows registry, as a custom subkey. The credentials are not stored in a Web-accessible directory, which decreases the potential visibility of the string. However, this approach complicates deployment, since installing the application requires changes to the target computer's registry. For additional security, you can use access control lists (ACLs) to restrict access to the registry subkey and encrypt the connection string within the subkey.
    Note   For further information about storing database credentials, see Database Security.

If you do store credentials as part of an application, you should make an attempt to protect them with encryption. One possibility is to use the Data Protection API (DPAPI). For details, see the MSDN technical article Windows Data Protection.

To store credentials in Web.config

  1. In the Web.config file, create a new <add> key in the <appSettings> element. The <appSettings> 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, labeled "mappedname" and "mappedkey."

    Tip   It is a good idea to avoid using the strings "password" and "user name" (or "username") in your application, so that malicious users searching for those terms will not find them.
    <appSettings>
       <add key="mappedname" value="MyWebAppUser"/>
       <add key="mappedkey" value="Aje$31"/>
    </appSettings>
    
  2. In your application, read the credentials from the static AppSettings property of the System.Configuration.ConfigurationSettings class.

    The following example shows how you can read credentials at run time and concatenate them into a connection string:

    ' Visual Basic
    Dim connectionString As String
    Dim user As String = System.Configuration.ConfigurationSettings. _
        AppSettings("mappedname")
    Dim pass As String = System.Configuration.ConfigurationSettings. _
        AppSettings("mappedkey")
    connectionString =  "data source=myserver;initial catalog=northwind"
    connectionString &= ";user id=" & user
    connectionString &= ";password=" & pass
    SqlConnection1.ConnectionString = connectionString
    
    // C#
    String connectionString;
    String user = System.Configuration.ConfigurationSettings.
        AppSettings["mappedname"];
    String pass = System.Configuration.ConfigurationSettings.
        AppSettings["mappedkey"];
    connectionString =  "data source=myserver;initial catalog=northwind";
    connectionString += ";user id=" + user;
    connectionString += ";password=" + pass;
    sqlConnection1.ConnectionString = connectionString;
    

To store credentials in the registry

  1. Add new subkeys to the SOFTWARE key in the HKEY_LOCAL_MACHINE hive.
  2. In your application, access the subkey using the OpenSubKey method of the Registry.LocalMachine object.
    Note   You must import the System and Microsoft.Win32 namespaces in order to use the Registry class.

    The following example illustrates how to get a user name and password (stored as string values called "mappedname" and "mappedkey") from the subkey "Connection":

    ' Visual Basic
    Dim user, pass, connectionString As String
    user = Microsoft.Win32.Registry.LocalMachine.OpenSubKey( _
       "Software\Connection").GetValue("mappedname").ToString()
    pass = Microsoft.Win32.Registry.LocalMachine.OpenSubKey( _
       "Software\Connection").GetValue("mappedkey").ToString()
    connectionString = "data source=myserver;initial catalog=northwind"
    connectionString &= ";user id=" & user
    connectionString &= ";password=" & pass
    SqlConnection1.ConnectionString = connectionString
    
    // C#
    String user, pass, connectionString;
    user = Microsoft.Win32.Registry.LocalMachine.
        OpenSubKey("Software\\Connection").
        GetValue("mappedname").ToString();
    pass = Microsoft.Win32.Registry.LocalMachine.
        OpenSubKey("Software\\Connection").
        GetValue("mappedkey").ToString();
    connectionString = "data source=myserver;initial catalog=northwind";
    connectionString += ";user id=" + user;
    connectionString += ";password=" + pass;
    sqlConnection1.ConnectionString = connectionString;
    

See Also

Accessing SQL Server from a Web Application | Access Permissions for Web Applications | ASP.NET Settings Schema | Database Security | ASP.NET Authentication | Security Portal

Show:
© 2016 Microsoft