SQL Server Connection Strings

This is the first of a series of posts on SQL Server connection strings. I don’t think connection strings are all that complicated but I often see developers have problems because they simply cloned an existing connection string (or found one on the internet) and tweaked it for the task at hand without really understanding what the keywords and values mean. This often results in run-time errors that can be tricky to diagnose. 
In this post, I’ll provide a connection string overview and discuss SqlClient connection strings and examples. I’ll discuss OLE DB and ODBC (used via ADO or ADO.NET) and JDBC in more detail the future articles.
Overview
SQL Server can be accessed using several technologies, each of which has different connection string particulars. Connection strings are provider/driver specific so one first needs to decide on a client API before formulating the proper string can be created. 
All connection strings share the same basic format, name/value pairs separated by semicolons, but the actual connection string keywords may vary by provider. Which keywords are required or optional also vary by provider and providers often share the same keywords (or provide synonyms) to minimize the connection string changes when switching between different providers. Most connection string keywords are optional and need to be specified only when the default is not appropriate. Connection string values should be enclosed in single or double quotes when the value may include a semicolon or equal sign (e.g. Password=”a&==b=;1@23″)
The purpose of a connection string is to supply a SQL Server provider/driver with the information needed to establish a connection to a SQL Server instance and may also be used to specify other configuration values, such as whether connection pooling is used. At the end of the day, the provider/driver needs to know at least:
·         SQL Server name (or address)
·         Authentication method (Windows or SQL Server)
·         Login credentials (login and password for SQL Server authentication)
SqlClient
One typically uses the .Net Framework Provider for SQL Server (abbreviated to SqlClient here) in managed code and a SQL Server OLE DB provider or ODBC driver from unmanaged code. It is possible to use OLE DB or ODBC for SQL Server data access in managed code but there is seldom a reason to do so since SqlClient offers high-performance access to SQL Server natively.
The authoritative reference for SqlClient connection strings is http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring.aspx. My goal is not to rehash all of the keywords or illustrate the many combinations here but rather show the ones most commonly used along with best practices. I use the primary keywords rather than synonyms or equivalent keywords in the examples.
The SqlConnectionStringBuilder class provides a programmatic way to build connection strings needed by SqlConnection class. The nice thing about SqlConnectionStringBuilder is that it provides IntelliSense and avoids connection string typos. It should always be used when constructing connection strings based in user input (e.g. user id and password prompt). But you still need to know which connection string properties (keywords) you need to set along with the default values. The examples here apply regardless of whether or not you use yjr SqlConnectionStringBuilder class.
SqlClient Connection String Keyword Examples
Unlike other providers, there is no “Provider” or “Driver” connection string keyword in a SqlClient connection string.  The .Net Framework Provider for SQL Server is implicit with a SqlConnection class so it is redundant to also specify the provider.
I’ll start with the minimal keyword(s) needed. The minimal SqlClient connection string need only specify the authentication method.  The example below specifies Windows authentication using “Integrated Security=SSPI”. This connection string will connect the default instance on the local machine using Windows authentication under the current process Windows security credentials. 

Integrated Security=SSPI
Listing 1: Connect to local default instance using Windows authentication
To connect to the local default instance using SQL authentication, just specify the credentials using the “User ID” and “Password” keywords instead of “Integrated Security=SSPI” keyword. SQL authentication is the default when “Integrated Security” or “Trused_Connection” keyword is not specified. Although I commonly see “Persist Security Info=False” also specified (a best practice from a security perspective), that is the default setting and may be omitted. Be aware that you should encrypt connection strings (or passwords in general) stored in configuration files when using SQL authentication.

User ID=MyLogin;Password=MiP@ssw0rd
Listing 2: Connect to local default instance using SQL authentication
One often connects to a remote SQL Server. Along with the authentication method, add the Data Source keyword to specify the desired SQL Server name or network address.

Data Source=SQLSERVERNAME;Integrated Security=SSPI
Listing 3: Connect to default instance on host SQLSERVERNAME using Windows authentication

Data Source=SQLSERVERNAME;User ID=MyLogin;Password=MiP@ssw0rd
Listing 4: Connect to instance on host SQLSERVERNAME using SQL authentication
Note that these same connection strings may be used to connect locally or remotely. Personally, I recommend always specifying the Data Source even when connecting locally. This makes it easy to move the application to another machine using with the same configuration and helps avoid oversights.
It is usually best to let SqlClient determine the appropriate network library to use rather than an explicit specification. SqlClient will figure out the appropriate network library based on the specified Data Source value. When you connect to a local instance using an unqualified name (or the value “(local)”), Shared Memory is used by default. SqlClient will use TCP/IP if a FQDN (e.g. SQLSERVERNAME.MyDOMAIN.COM) or IP address is specified regardless of whether the instance is local or remote. Since TCP/IP is most commonly used nowadays, I’ll focus on TCP/IP in this article and use a FQDN in the subsequent examples to avoid ambiguity.
It is often desirable to specify the initial database context in the connection sting. If omitted, the default database of the authenticated account is used. This is accomplished using either the “Initial Catalog” or “Database” keyword. I suggest always including the “Initial Catalog” keyword.

Data Source=SQLSERVERNAME.MYDOMAIN.COM;Integrated Security=SSPI;Initial Catalog=MyDatabase
Listing 4: Connect to default instance on host SQLSERVERNAME using Windows authentication with initial database context of MyDatabase
Named Instances
The connection strings I’ve shown so far assume the target is a default SQL Server instance listening on port 1433. One can run multiple instances of SQL Server on the same host using the named instance feature. If your target database instance is a named instance, SqlClient will also need to know the instance name or instance port number. The instance name can be specified by appending a backslash and instance name to the Data Source value:

Data Source=SQLSERVERNAME.MYDOMAIN.COM\MYINSTANCE;Integrated Security=SSPI;Initial Catalog=MyDatabase
Listing 5: Connect to named instance on host SQLSERVERNAME using Windows authentication with initial database context of MyDatabase
As an aside, I often see connectivity problems with named instances due to oversights in the SQL Server configuration. When an instance name is specified, SqlClient interrogates the SQL Server Brower service on the SQL Server host to determine the instance port (or named pipe name). The SQL Server Brower service is disabled by default so you need to enable and start it in order to connect by the instance name. This can be done using the SQL Server Configuration Manager tool. Also, since the SQL Server Brower service communicates over UDP port 1434, that port must be allowed through firewalls.
You can specify a port number instead of instance name to directly to a named instance (or to a default instance listing on a non-standard port). The port may be specified by appending a comma and port number to the data source value. The needed port number can be ascertained from the SQL Server Configuration Manager tool.

Data Source=SQLSERVERNAME.MYDOMAIN.COM,60086;Integrated Security=SSPI;Initial Catalog=MyDatabase
Listing 6: Connect to instance on host SQLSERVERNAME listening on port 60086 using Windows authentication with initial database context of MyDatabase
Additional Keywords

In addition to the “Data Source”, “Initial Catalog” and “Integrated Security” (or “User Id” and “Password”) keywords I’ve discussed so far, I recommend that “Application Name” also be specified. The specified string is helps identify the application when monitoring activity on the database server. This is especially useful when an application server or client hosts multiple applications.

Data Source=SQLSERVERNAME.MYDOMAIN.COM;Integrated Security=SSPI;Initial Catalog=MyDatabase;Application Name=Connection String Example
Listing 7: Connect to default instance on host SQLSERVERNAME using Windows authentication with initial database context of MyDatabase with application name specification
In my opinion, the many other keywords are noise unless the default values are inappropriate for your environment. 
Summary

You can get by nicely in most cases with only the 4 or 5 SqlClient connection string keywords I’ve discussed here. I suggest you establish a connection string standard that includes the “Data Source”, “Initial Catalog”, “Application Name” keywords plus the authentication method, “Integrated Security=SSPI” or “User Id” and “Password”.

Why Parameters are a Best Practice

Parameterized queries are arguably the single most important database development best practice. Parameters, with proper SQL data types, provide many benefits. These include:

– improve security by preventing injection of unwanted SQL (barring non-parameterized server-side dynamic SQL)
– eliminate need to enclose literals in quotes (or not) depending on data type
– eliminate need prefix Unicode literal strings with N
– allow single quotes within strings without the need to escape them
– avoid the need to format date/time/datetime string literals in a particular way (which vary by culture)
– do not require decimal separators (which vary by culture)
– improve performance by reducing compilation costs and promoting execution plan cache reuse
– allow WHERE clause predicates with Always Encrypted columns
– code that’s cleaner and easier to maintain

Despite these many benefits, it is all too common to see applications build SQL statements by concatenating program variable values as SQL literals with values that vary by execution. I believe this simply due to ignorance because one often learns SQL using an ad-hoc tool like SSMS in order to focus on SQL syntax and database concepts, overlooking the important database programming aspects needed in practice. Developers unwittingly build SQL queries in application code as if they were typing SQL statements into an SSMS query window. Sadly, instructors sometimes teach database programming using string concatenation instead of parameterized queries and many online examples use improper techniques too, spreading this poor practice. When one posts questions with non-parameterized query code in online help forums, the egregious practice often distracts answerers from addressing the real question, attracts downvotes, and in some cases, a parameterized query would have avoided the need for the question to begin with.

This article discusses parameter basics in commonly used programming languages and Microsoft SQL Server APIs, along with code examples and best practices. Additionally, internals of how parameters are actually sent to SQL Server are described to provide insight into why parameterized queries are so important. This article focuses specifically on Microsoft SQL Server but parameters and their benefits apply to other DBMS products as well.

Introduction

Here’s a C# example of the common mistake of building an ad-hoc query with string concatenation:

connectionString = "Data Source=.;Initial Catalog=YourDatabase;Integrated Security=SSPI";
using (var connection = new SqlConnection(connectionString))
using (var selectCommand =
    new SqlCommand("SELECT UserName, FirstName, LastName FROM dbo.UserTable WHERE UserName = '" + userName + "';", connection))
{
    connection.Open();
    using (var reader = selectCommand.ExecuteReader())
    {
        while (reader.Read())
        {
            Console.WriteLine($"{reader["UserName"]} {reader["FirstName"]} {reader["LastName"]}");
        }
    }
}

A common misconception is that using stored procedures inherently prevent SQL injection. Stored procedures can help mitigate security risks but must also be called properly to release parameterization benefits. The same problems as the previous ad-hoc query exists with the stored procedure example below because parameter values are passed as literals.

connectionString = "Data Source=.;Initial Catalog=YourDatabase;Integrated Security=SSPI";
using (var connection = new SqlConnection(connectionString))
using (var executeCommand =
            new SqlCommand("EXECUTE dbo.usp_GetUser '" + userName + "';", connection))
{
    connection.Open();
    using (var reader = executeCommand.ExecuteReader())
    {
        while (reader.Read())
        {
            Console.WriteLine($"{reader["UserName"]} {reader["FirstName"]} {reader["LastName"]}");
        }
    }
}

Techniques for building parameterized SQL statements vary by client API, driver, programming language, and/or framework. The underlying concept is the same, though. Instead of including literals in SQL statements for values that differ for each execution, specify parameter tokens in their place and add parameters with values to the command separately. The SQL statement itself need not change between executions but parameter values can differ for each execution without modifying the SQL statement itself.

The parameter creation techniques used here are not the only ones available to create parameters. I’ve included documentation links that show other parameter creation methods and guidance as to which should be avoided.

Most ORM frameworks (Entity Framwork, Hibernate, et.al.) build parameterized queries on your behalf. These are beyond the focus of this article but I suggest you peruse the Parameters Under the Covers section of this article to ensure parameters are indeed being passed as expected as an RPC request by the framework you are using.

Parameters with ADO.NET

I’ll first mention one should use an ADO.NET and a managed ADO.NET provider in .NET applications whenever possible. ADO.NET does allow one to use unmanaged ODBC and OLE DB drivers via the System.Data.Odbc and System.Data.OleDb namespace objects but those are intended to be used only when no suitable managed ADO.NET provider is available. For Microsoft SQL Server, the managed providers are System.Data.SqlClient (included with the .NET framework) or the newer Microsoft.Data.SqlClient (.NET Core package, currently in preview). Managed providers provide the best performance in .NET applications by avoiding the cost of interop with unmanaged code, which is especially costly with large result sets.

Also, it’s not uncommon to see VB.NET applications sometimes use the legacy ADODB (ADO classic) API instead of ADO.NET because they started life in the VB6 world and were never changed to use ADO.NET. I’ll discuss ADO classic later in the context of unmanaged code. I suggest such lacy code be remediated to use ADO.NET if possible.

Below are C# parameterized equivalents of the earlier queries. These same objects and techniques can be used in VB.NET and managed C++ with only linguistic differences. The only change to the original SELECT statement is the parameter tokens within the query text plus adding parameters to the command’s Parameters collection (instead of concatenating values as SQL literal text). Parameter names (‘@’ prefix) are used as tokens for parameter values in SqlClient queries and allow for mnemonic names to make it easy to identify individual parameters within the query.

Importantly, note that parameter tokens within a query are never enclosed in quotes because these are not literals. Also, the same parameter may be specified more than once within a query with SqlClient without the need to add an additional parameter with the same value.

connectionString = "Data Source=.;Initial Catalog=YourDatabase;Integrated Security=SSPI";
        using (var connection = new SqlConnection(connectionString))
        using (var selectCommand =
                new SqlCommand("SELECT UserName, FirstName, LastName FROM dbo.UserTable WHERE UserName = @UserName;", connection))
        {
            selectCommand.Parameters.Add("@UserName", SqlDbType.VarChar, 50).Value = userName;
            connection.Open();
            using (var reader = selectCommand.ExecuteReader())
            {
                while (reader.Read())
                {
                    Console.WriteLine($"{reader["UserName"]} {reader["FirstName"]} {reader["LastName"]}");
                }
            }
        }

It’s generally best to specify CommandType.StoredProcedure when calling a stored procedure because this reduces server-side parsing and enforces that stored procedure parameter arguments be passed as client parameters instead of literals. With SqlClient and CommandType.StoredProcedure, only the stored procedure name is specified as the command text (i.e. no parameter tokens). The procedure name should be schema-qualified for maximum performance, a consideration in high-volume OLTP workloads. Parameters are added to the Parameters collection and implicitly mapped to the actual stored procedure parameters by name rather than by ordinal so the parameter collection order does not matter when multiple parameters are involved.

        connectionString = "Data Source=.;Initial Catalog=YourDatabase;Integrated Security=SSPI";
        using (var connection = new SqlConnection(connectionString))
        using (var executeCommand =
            new SqlCommand("dbo.usp_GetUser", connection))
        {
            executeCommand.CommandType = CommandType.StoredProcedure;
            executeCommand.Parameters.Add("@UserName", SqlDbType.VarChar, 50).Value = userName;
            connection.Open();
            using (var reader = executeCommand.ExecuteReader())
            {
                while (reader.Read())
                {
                    Console.WriteLine($"{reader["UserName"]} {reader["FirstName"]} {reader["LastName"]}");
                }
            }
        }

One can also execute a stored procedure as a parameterized query using a T-SQL EXECUTE statement and CommandType.Text similarly to a parameterized query. There is no benefit in doing so compared to CommandType.StoredProcedure for a single proc call (and it does slightly bloat the request). However, CommandType.Text allows one to execute a parameterized batch of multiple statements in a single round trip. The batch may be a mix of EXECUTE and other DML statements.

With an EXECUTE statement, stored procedure parameters can be specified using either named (i.e. @stored_proc_parameter_name1 = @command_parameter_name1, @stored_proc_parameter_name2 = @command_parameter_name2, …) or positional (@command_parameter_name1, @command_parameter_name2, …) arguments. Stating the obvious, positional syntax necessitates the parameter argument order on the EXECUTE statement match the stored procedure definition order. However, the order of parameters within the parameters collection isn’t significant with either named or positional arguments because the parameters collection is mapped to the parameters within the SQL text by name with SqlClient.

Below is a named parameter example. The stored procedure parameter name and the client parameter name are the same in this example.

connectionString = "Data Source=.;Initial Catalog=YourDatabase;Integrated Security=SSPI";
        using (var connection = new SqlConnection(connectionString))
        using (var executeCommand =
            new SqlCommand("EXECUTE dbo.usp_GetUser @UserName = @UserName", connection))
        {
            executeCommand.CommandType = CommandType.Text;
            executeCommand.Parameters.Add("@UserName", SqlDbType.VarChar, 50).Value = userName;
            connection.Open();
            using (var reader = executeCommand.ExecuteReader())
            {
                while (reader.Read())
                {
                    Console.WriteLine($"{reader["UserName"]} {reader["FirstName"]} {reader["LastName"]}");
                }
            }
        }

Note that the EXECUTE keyword could have be omitted in this example because SQL Server assumes the first statement of a batch is EXECUTE. I recommend the EXECUTE be explicitly specified even when the first statement in a batch.

Parameters with ADO.NET System.Data.OleDb

Again, .NET applications should always use a managed provider when one is available (SqlClient in the case of SQL Server). That being said, I use unmanaged SQL Server drivers in these examples to illustrate how queries can be parameterized in ADO.NET for other DBMS products that don’t have a managed .NET provider. The only difference would be the connection string.

Here are the equivalent OLE DB parameterized queries in ADO.NET using the latest SQL Server OLE DB provider. The differences compared to same SqlClient query are the connection string, question mark parameter tokens, and the System.Data.OleDb objects.

        connectionString = "Provider=MSOLEDBSQL;Data Source=.;Initial Catalog=YourDatabase;Integrated Security=SSPI";
        using (var connection = new OleDbConnection(connectionString))
        using (var selectCommand =
                new OleDbCommand("SELECT UserName, FirstName, LastName FROM dbo.UserTable WHERE UserName = ?;", connection))
        {
            selectCommand.Parameters.Add("@UserName", OleDbType.VarChar, 50).Value = userName;
            connection.Open();
            using (var reader = selectCommand.ExecuteReader())
            {
                while (reader.Read())
                {
                    Console.WriteLine($"{reader["UserName"]} {reader["FirstName"]} {reader["LastName"]}");
                }
            }
        }
        connectionString = "Provider=MSOLEDBSQL;Data Source=.;Initial Catalog=YourDatabase;Integrated Security=SSPI";
        using (var connection = new OleDbConnection(connectionString))
        using (var executeCommand =
                new OleDbCommand("dbo.usp_GetUser", connection))
        {
            executeCommand.CommandType = CommandType.StoredProcedure;
            executeCommand.Parameters.Add("@UserName", OleDbType.VarChar, 50).Value = userName;
            connection.Open();
            using (var reader = executeCommand.ExecuteReader())
            {
                while (reader.Read())
                {
                    Console.WriteLine($"{reader["UserName"]} {reader["FirstName"]} {reader["LastName"]}");
                }
            }
        }

Parameters with ADO.NET System.Data.Odbc

Below is the ODBC parameterized query in ADO.NET. The only difference compared to the earlier OLE DB version is the connection string and System.Data.Odbc namespace objects. Although this example uses a DSN-less connection string, one could also specify an ODBC DSN in the connection string (e.g. “DSN=YourDSN”) and omit the attributes that are redundant with the DSN configuration.

        connectionString = "Driver={ODBC Driver 17 for SQL Server};Server=.;Database=YourDatabase;Trusted_Connection=Yes";
        using (var connection = new OdbcConnection(connectionString))
        using (var selectCommand =
            new OdbcCommand("SELECT UserName, FirstName, LastName FROM dbo.UserTable WHERE UserName = ?;", connection))
        {
            selectCommand.Parameters.Add("@UserName", OdbcType.VarChar, 50).Value = userName;
            connection.Open();
            using (var reader = selectCommand.ExecuteReader())
            {
                while (reader.Read())
                {
                    Console.WriteLine($"{reader["UserName"]} {reader["FirstName"]} {reader["LastName"]}");
                }
            }
        }

Similarly, here is the ODBC version of the same previous stored procedure calls. However, with ODBC and CommandType.StoredProcedure, the command text specifies an ODBC procedure call escape sequence with parameter marker(s) rather than just the stored procedure name.

    connectionString = "Driver={ODBC Driver 17 for SQL Server};Server=.;Database=YourDatabase;Trusted_Connection=Yes";
        using (var connection = new OdbcConnection(connectionString))
        using (var executeCommand =
            new OdbcCommand("{call dbo.usp_GetUser(?)}", connection))
        {
            executeCommand.CommandType = CommandType.StoredProcedure;
            executeCommand.Parameters.Add("@UserName", OdbcType.VarChar, 50).Value = userName;
            connection.Open();
            using (var reader = executeCommand.ExecuteReader())
            {
                while (reader.Read())
                {
                    Console.WriteLine($"{reader["UserName"]} {reader["FirstName"]} {reader["LastName"]}");
                }
            }
        }

Parameters with ADO Classic

ADO “Classic” (not to be confused with ADO.NET) is a mature legacy technology that hasn’t been enhanced for about 20 years. An unmanaged COM-based API, ADO classic is still used today in classic ASP web applications, VBA (Visual Basic for Applications), legacy VB 6 code, and VB.NET code that was converted from VB 6 without being refactored to use modern managed ADO.NET APIs.

It is best to use an OLE DB driver with ADO classic because these unmanaged APIs natively use COM-based technologies. That being said, one can use ODBC (which is ultimately a call-level interface rather than COM) with ADO via the Microsoft OLE DB Provider for ODBC drivers (MSDASQL, which is the default OLE DB provider when no provider is specified). This is needed when no OLE DB provider available for a non SQL Server DBMS but should otherwise be avoided since ADO must go through the extra MSDASQL layer. A SQL Server OLE DB provider/driver (i.e. SQLOLEDB, SQLNCLI, and MSOLEDBSQL) should be used directly to access SQL Server from ADO classic.

Regardless of the underlying driver, ADO programming and parameterization is similar with either OLE DB or ODBC. The ADO classic object model is different than its ADO.NET cousin but parameterization concepts and techniques are similar. I’ve included C# examples here only to illustrate the ADO parameter object model differences compared to the previous ADO.NET examples. In practice, one would not typically use ADO classic in managed code.

Parameterized ADO query example:

        connectionString = "Provider=MSOLEDBSQL;Data Source=.;Initial Catalog=YourDatabase;Integrated Security=SSPI";
        var connection = new ADODB.Connection();
        connection.Open(connectionString);
        var selectCommand = new ADODB.Command();
        selectCommand.ActiveConnection = connection;
        selectCommand.CommandText = "SELECT UserName, FirstName, LastName FROM dbo.UserTable WHERE UserName = ?;";
        selectCommand.Parameters.Append(
        selectCommand.CreateParameter("@UserName", ADODB.DataTypeEnum.adVarChar, ADODB.ParameterDirectionEnum.adParamInput, 50, userName));
        object recordCount = 0;
        var recordSet = selectCommand.Execute(out recordCount);
        while (!recordSet.EOF)
        {
            Console.WriteLine($"{recordSet.Fields["UserName"].Value} {recordSet.Fields["LastName"].Value} {recordSet.Fields["LastName"].Value}");
            recordSet.MoveNext();
        }
        recordSet.Close();
        connection.Close();

Parameterized ADO proc call example:

        connectionString = "Provider=MSOLEDBSQL;Data Source=.;Initial Catalog=YourDatabase;Integrated Security=SSPI";
        var connection = new ADODB.Connection();
        connection.Open(connectionString);
        var executeCommand = new ADODB.Command();
        executeCommand.ActiveConnection = connection;
        executeCommand.CommandText = "dbo.usp_GetUser";
        executeCommand.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc;
        executeCommand.Parameters.Append(
        executeCommand.CreateParameter("@UserName", ADODB.DataTypeEnum.adVarChar, ADODB.ParameterDirectionEnum.adParamInput, 50, userName));
        object recordCount = 0;
        var recordSet = executeCommand.Execute(out recordCount);
        while (!recordSet.EOF)
        {
            Console.WriteLine($"{recordSet.Fields["UserName"].Value} {recordSet.Fields["LastName"].Value} {recordSet.Fields["LastName"].Value}");
            recordSet.MoveNext();
        }
        recordSet.Close();
        connection.Close();

ADO Parameters with VBScript

Below is an example stand-alone VBScript example of the query and proc call.

connectionString = "Provider=MSOLEDBSQL;Data Source=.;Database=YourDatabase;Integrated Security=SSPI"
Set connection = CreateObject("ADODB.Connection")
Set command = CreateObject("ADODB.Command")
connection.Open connectionString
command.ActiveConnection = connection
command.CommandText = "SELECT UserName, FirstName, LastName FROM dbo.UserTable WHERE UserName = ?;"
Set parmUserName = command.CreateParameter("@UserName", adVarChar, adParamInput, 50, strUserName)
command.Parameters.Append(parmUserName)
connectionString = "Provider=MSOLEDBSQL;Data Source=.;Database=YourDatabase;Integrated Security=SSPI"
Set connection = CreateObject("ADODB.Connection")
Set command = CreateObject("ADODB.Command")
command.ActiveConnection = connection
command.CommandType = adCmdStoredProcedure
command.CommandText = "dbo.usp_GetUser"
Set parmUserName = command.CreateParameter("@UserName", adVarChar, adParamInput, 50, strUserName)
command.Parameters.Append(parmUserName)

ADO Parameters with ASP classic

The ADO code for classic ASP is the same as the preceding stand-alone VBScript example except that the objects are instantiated using the IIS Server.CreateObject method:

connectionString = "Provider=MSOLEDBSQL;Data Source=.;Database=tempdb;Integrated Security=SSPI"
Set connection = Server.CreateObject("ADODB.Connection")
Set command = Server.CreateObject("ADODB.Command")
command.ActiveConnection = connection
command.CommandText = "SELECT UserName, FirstName, LastName FROM dbo.UserTable WHERE UserName = ?;"
Set parmUserName = command.CreateParameter("@UserName", adVarChar, adParamInput, 50, strUserName)
command.Parameters.Append(parmUserName)
connectionString = "Provider=MSOLEDBSQL;Data Source=.;Database=tempdb;Integrated Security=SSPI"
Set connection = Server.CreateObject("ADODB.Connection")
Set command = Server.CreateObject("ADODB.Command")
command.ActiveConnection = connection
command.CommandType = adCmdStoredProcedure
command.CommandText = "dbo.usp_GetUser"
Set parmUserName = command.CreateParameter("@UserName", adVarChar, adParamInput, 50, strUserName)
command.Parameters.Append(parmUserName)

Parameters Under the Covers

The job of all SQL Server client APIs regardless of programming language is to manage the Tabular Data Stream (TDS) protocol session used for SQL Server communication. This involves establishing the session (network connection and authentication), converting application requests into internal TDS request messages (e.g. batch and RPC requests), sending those to SQL Server, and receiving the results returned by SQL Server over TDS. Although details of the TDS protocol session are an implementation detail transparent to applications, delving into the way TDS requests are sent to the database engine provides valuable insight into the concepts, benefits, and use of parameters in the SQL Server world.

When a non-parameterized query is executed (i.e. an empty parameter collection and CommandType.Text in ADO.NET), the client API sends the command text to SQL Server as a SQL batch request over TDS. There is no provision for parameters in a SQL batch request. The database engine parses the batch text (which may contain multiple statements), generates an execution plan, and executes the batch. If the batch text (including literal values) exactly matches that of a previously executed query (or an auto-parameterized query) that is still cached, the plan is reused. Because a batch request has no parameters, the application must include T-SQL literals within the text for values that vary by execution. This requires the app code to build the query by concatenating SQL language text with literal strings and for SQL Server to parse those literals.

The onus is on the application to construct literal strings embedded within the SQL batch text properly so that they are parsed as desired by SQL Server. This necessitates enclosing literal values in single quotes when required and formatting the string value according to the data type it represents. The app code must double up single quotes within string literal values so that it’s interpreted as a quote instead of literal enclosure. Date/time literals should be formatted as ISO 8601 literal values to ensure the intended value is used regardless of session the DATEFORMAT setting, a common mistake with non-parameterized queries. Decimal literals must specify the ANSI/ISO standard period as the decimal separator instead of a comma used by some cultures. Source values must be validated to ensure adherence to the intended data type and not interpreted as SQL language elements (injecting unintended SQL).

Parameterized queries (e.g. non-empty parameters collection or CommandType.StoredProcedure) are processed differently. The client API sends the command to SQL Server as a remote procedure call (RPC batch) TDS request instead of a SQL batch request. This RPC batch request includes the batch text with the supplied parameter tokens (e.g. CommandType.Text) or stored procedure (e.g. CommandType.StoredProcedure). The actual parameters, containing parameter definition meta-data and values as specified by the client application, are included in separate structures as part the TDS RPC request stream sent to SQL Server. Most importantly, the actual parameter values as passed as native data types as specified by the client application. This has a many positive implications.

The database engine need not parse parameter values since they are already in the native structure of the specified parameter data type, such as a 32-bit integer, datetime structure, decimal structure, Unicode string, etc. SQL Server only checks that parameter values adhere to the TDS protocol spec and then uses the supplied parameter values directly without parsing. There is no notion of datetime formatting, string escape sequences, decimal separators, etc. because no parsed occurs. Unwanted SQL cannot be injected via parameters (barring server-side dynamic SQL) because the values are not part of a T-SQL statement.

I’ll add that the query text you see in a server-side trace of an RPC request is not necessarily what SQL Server actually received by the client API. The query text in trace data is actually a reversed-engineered rendering of the TDS protocol request in familiar T-SQL format. For example, when you pass a table-valued parameter, you’ll see an RPC batch request with batch text containing table variable declared, a bunch of INSERT statements into the variable, followed by it’s use as a parameter in the query or stored procedure. In actuality, the TVP column meta-data tokens were passed over TDS followed by the stream of TVP rows. The database engine bulk-inserted the TVP data into a tempdb structure matching the column meta-data, not individual inserts as the trace suggested.

Similarly, scalar parameter values will show as literal representations in the trace instead of the actual binary structure passed. For example, a datetime value will show as a ‘yyyy-MM-dd HH:mm:ss.fff’ literal (not an unambiguous ISO 8601 datetime format) rather than the actual binary datetime structure passed to SQL Server.

Much of this is implementation details but something you should be aware of when interpreting RPC requests in traces.

Best Practices

Parameter definitions should specify the desired data type, maximum length, precision, and scale of each parameter. Attention to these parameter definition details will help maximize performance by avoiding implicit conversions, promoting sargable expressions, and reusable cached plans.

A common problem (i.e. mistake) is using the AddWithValue method to create parameters. AddWithValue infers the data type and length based on the client variable value and may result in non-sargable expressions and cache bloat as I detailed in this post. The bottom line is that one should use a method that allows specification of the desired SqlDbType, length, etc. such as Add.

Security with Ownership Chains

Security with Ownership Chains

 

Ownership chaining is one of my favorite SQL Server security features.  I like ownership chaining because it allows me to confine data access to specific stored procedures, views and functions while preventing direct access to the underlying tables.  This approach provides a well-defined application interface that insulates applications from the physical implementation and allows me to change things on the back-end as long as I don’t change the interface.  Also, thanks to ownership chaining, I have many options to vertically and horizontally partition data to meet security objectives while ensuring tables are not accessed directly.  

Ignorance is not bliss when it comes to ownership chaining security, though.  I’ve seen cases where data access was inadvertently allowed because ownership chaining wasn’t considered.  It is imperative that SQL Server DBAs understand ownership chaining in the SQL Server security model so that data is properly secured.  I want to point out a couple of important security concepts as it relates to ownership chaining with the hope that it will help SQL Server DBAs implement a security scheme that is appropriate for their environment and also easy to manage.

Ownership Chain Behavior

SQL Server always checks permissions on directly referenced objects using the current connection security context.  However, permissions are evaluated differently when objects are accessed indirectly, such as those used in a trigger, view or stored procedure.  The behavior of ownership chaining is that object permissions are not evaluated when the calling and referenced object owners are the same.  Also, because object permission checking is short-circuited in an unbroken chain, ownership chaining takes precedence over an impersonated security context (EXECUTE AS clause) as well as denied object permissions.   Consequently, permissions on indirectly referenced objects are irrelevant in an unbroken ownership chain.  This ownership chaining behavior gives a DBA control over exactly who can use which objects while preventing ad-hoc access to other objects.

Ownership chaining behavior might not be intuitive at first.  I like to think of it from the perspective that the purpose of granting object permissions is so the object can actually be used.  For example, it is of no use to grant a user SELECT permissions on a view unless data can be retrieved from the view.  The same applies even when a stored procedure or function impersonates with EXECUTE AS; the impersonated principal must be able to use objects referenced by the proc in order for the procedure to be useful.  I keep in mind that with ownership chaining, I effectively grant permissions on exposed object functionality regardless of how the functionality is implemented.

Ownership chaining is limited to a single owner in order to prevent escalation of privileges in environments where non-privileged users can create objects.  Ownership chaining also does not apply to dynamic SQL executed from a stored procedure; the invoking (or impersonated) user needs permissions on objects referenced by dynamic SQL statements.  I should add that a one of the useful purposes of EXECUTE AS and signing modules with certificates is to provide needed permissions in a broken ownership chain or granting statement permissions without granting user permissions directly.  See SQL Server MVP Erland Sommarskog’s article Giving Permissions through Stored Procedures for a discussion of these techniques.

Ownership Best Practices

Ownership is obviously central to ownership chaining.  In the vast majority of environments, it’s probably best to simply inherit the owner (a.k.a. AUTHORIZATION) from the object’s schema instead of overriding on individual objects.  It is possible to assign ownership to individual objects (using ALTER AUTHORIZATION) in order to implement chaining that is independent of schema but I think this is overly complex for most environments.  One reason I can think of why one might specify a different owner at the object level is in the case where a table contains particularly sensitive data and one wants to deliberately prevent inadvertent access via ownership chaining.  I think such cases are rare, though. 

I described some of the practices I follow for schema and object ownership in Keep Schema and Ownership Simple.  In summary, I specify dbo as the schema authorization (owner) unless I want to break the ownership chain between schemas to create a security boundary.  When a security boundary is desired, I create a role (or user without login) with the same name as the schema for the schema authorization.