Understanding SQL Server Connection String

To be placed within the XML File – Usually Web.Config

<connectionStrings>

<add name=”AdventureWorks” connectionString=”Data Source=SQLServerAlias;Integrated Security=True;Initial Catalog=AdventureWorks2008R2″/>

</connectionStrings>


SQL Server connection string on a basic level has a three different attributes.


1) Data Source

Normally the Data Source attribute is set to a string known as an instance, which in its simplest form is known as a default instance. A default instance simply specifies the name of the computer where SQL Server is installed, such as “MYSERVER”. You can also use “(local)” if the SQL Server resides on the same server as your application, a fully qualified domain name “myserver.mycompany.com”, or you can specify a TCP/IP address and port number.

Sometimes it is useful to have multiple SQL Server installations on the same server. To accomplish this, SQL Server setup supports the ability to install a single default instance and additional named instances. A named instance takes the form “MYSERVER\MYINSTANCE”. You can find out the names of all of the SQL Server instances installed using the SQL Server Configuration Manager tool.

In this lab, the Data Source is set to a server alias (‘SqlServerAlias’). A server alias provides a handy way to reference a SQL Server instance from multiple applications so it can be updated in one place without having to modify application source code. You create and edit a server alias using the SQL Server Configuration Manager tool.

2) Integrated Security

This attribute tells ADO.NET what type of authentication to use to connect to SQL Server. When set to True, ADO.NET will attempt to use Windows Authentication to connect to SQL Server. Windows Authentication makes it easy to use existing security accounts defined in Windows or Active Directory instead of creating duplicate security accounts in the database. An added benefit is that you do not have to include security credentials in your connection string.

In order to successfully connect to SQL Server using Windows Authentication, an administrator must have previously granted connection privileges to the Windows security account using the Transact-SQL CREATE LOGIN command or using a tool like SQL Server Management Studio. This is analogous to an administrator granting privileges to access other resources like a file, directory or share. SQL Server setup requires the user who is performing the installation to create a login for at least one Windows security account. Setup makes this login a member of the server-level sysadmin role.

When the Integrated Security attribute is set to False, ADO.NET will attempt to use SQL Server Authentication to connect. SQL Server Authentication is useful when you do not have access to Windows or Active Directory security credentials, or if Windows Authentication is not supported in your environment. SQL Server Authentication must be enabled on the instance you are trying to connect to. It also requires that you specify two additional attributes in your connection string: “User ID” and “Password”. If you have to use SQL Server authentication, encrypt your connection strings to protect them.

In order to successfully connect to SQL Server using SQL Server Authentication, an administrator must have previously granted connection privileges using the Transact-SQL CREATE LOGIN command or using a tool like SQL Server Management Studio. Here’s an example of a connection string that uses SQL Server authentication:

To be placed within the XML File – Usually Web.Config

<connectionStrings>

<add name=”AdventureWorks” connectionString=”Data Source=SQLServerAlias;Integrated Security=False;Initial Catalog= AdventureWorks2008R2;User Id=userName;Password=userPassword“/>

</connectionStrings>

3)   Initial Catalog

Let us move on and talk about the Initial Catalog attribute. SQL Server not only supports multiple instances per server, but it also supports multiple databases per instance. This lab uses a database named “AdventureWorks2008R2”. Specifying an Initial Catalog in the connection string automatically switches the context of the current database when the application connects to ensure you are accessing the correct data, so it is important that you set it correctly to avoid making changes in the wrong database (like the master system database for example).

Because each database may have different security and access requirements, SQL Server supports a robust security architecture based upon security principals. There are three layers of principals, Windows-level principals, SQL Server-level principals and database-level principals, and each of them can be secured separately. So far we have only dealt with access control for Windows-level and SQL Server-level principals. These principals will get you connected to SQL Server, but you will need to understand and properly configure database-level principals to actually work with data.

The most common database-level principle is known as a database user. Database users are created in specific databases using the Transact-SQL CREATE USER command or a tool like SQL Server Management Studio. Each database user is associated with a server-level principal. In order to access or modify data, the database user performing the operation must have been granted privileges to do so using the Transact-SQL GRANT command or a tool like SQL Server Management Studio. Those privileges can be granted directly to the database user or to a database role that the database user is a member of. Examples of privileges include SELECT, UPDATE, INSERT, DELETE and EXECUTE (for running stored procedures).

Note that some server-level principles, such as those that belong to the server-level sysadmin role, are automatically associated with a high-privileged database user known as dbo. This is a special user in all SQL Server databases known as the “database owner”. The database owner can perform all actions in a particular database because it is a member of the db_owner database-level role. Since this lab assumes your Windows security account is a member of sysadmin, we are also assuming you can access the AdventureWorks2008R2 database and perform operations in it as the database owner.

Developers have the option of configuring their applications to connect to the database using the security credentials of the user, or alternatively using security credentials associated with the application (which in ASP.NET is known as an application identity). Using the user’s security credentials provides more flexibility in controlling access to data using built-in SQL Server functionality in an application-independent fashion, while using an application identity requires you to implement most data access control yourself in application logic, which is fine if only one application will access the data.

Reference: – SQL Server 2008 R2 Training Kit

  • SQL_DBA

    how will use an alias name of a sql server in a connection string?