SQL Server Connection String: Connecting to Your SQL Server from Your Code

sql server connection stringDatabases are the foundation for dynamic websites. For that reason, if you want to connect to a SQL Server from your code, you need to add it to your source code files. Most development languages have a way to include the SQL connection string in one file, and you can call this one file each time you need to connect to the database. The SQL connection string is a list of characters that specify to the code and web server how to connect to the database. If the string isn’t formatted or written correctly, you’ll receive an error.

Receive a comprehensive ASP.NET MVC overview and learn how to code dynamic websites

Defining a Connection String

A connection string is given the string data type. The following code is an example of a standard connection string:

string connString = “Server=sql_server_name;Database=database_name;User Id=user_name;Password=database_password;”;

In the above example, a connection string named “connString” is created. This connection string is a very basic string with no drivers specified. The first property named “server” identifies the name of the SQL Server. If the SQL Server database runs on the same machine as the web server, you can specify “localhost” for the SQL Server name. In this example, the SQL Server’s name is “sql_server_name.”

The next property is “database.” This property specifies the database name on the SQL Server machine. The database name is usually something similar to the application attempting to make the connection.

The next two properties are the user name and password. Most SQL Server setups are created in “Mixed Mode.” Mixed mode allows you to create a separate SQL Server user name and password that’s different than the network user name and password. This concept increases security on your database. If hackers gain access to your network, they need another user name and password to gain access to your database.

Learn how to work with a SQL Server at Udemy.com.

You can also use a trusted connection. Trusted connections use the currently logged in user for the connection. You’ll need to enable Windows logins to use this option. The following code is an example of a trust SQL Server connection string:

string connString = “Server=sql_server_name;Database=database_name;Trusted_Connection=True;”;

Notice the difference between the latest connection string and the previous one. The “trusted_connection” property set to true will use the Windows login to connect. This string is typically used when you are in development for a specific application.

Database administrators can use SQL Server instances to set up a database on the server. If this is the installation type, you won’t be able to connect without specifying the instance. The following connection string specifies an instance named “myinstance”:

string connString = “Server= sql_server_name \myinstance;Database= database_name;User Id= user_name;Password=database_password;”;

Incidentally, you can use an IP address for the server name instead of the server’s name. This is also an acceptable way to connect.

The previous connection strings assumed that you can use the default driver to connect to the SQL Server. You’ll need to specify the driver if SQL Server isn’t installed on the local host. You’ll also need this driver if you connect to SQL Server from another platform such as a PHP and Linux connection. The following code is an example of a connection string to SQL Server 2012 Express edition:

string connString = “Driver={SQL Server Native Client 11.0}; Database= database_name;User Id= user_name;Password=database_password;”;

Notice in the above code, all the settings are the same as before except the driver is specified. SQL Server Express is used for development projects, so you’ll need this driver if you are connecting to a development server during software development.

You don’t always connect to just a SQL Server. Sometimes, you’ll want to connect to another database engine such as Oracle or Access. You might also want to connect to a data file such as XML. When you perform this type of connection, you need to specify the file type and driver or your program won’t be able to understand the format.

For instance, the following code is an example of a connection string that connects to an Oracle server:

string connString = “Provider=OraOLEDB.Oracle;Data Source=oracle_dbname;User Id=user_name;Password=password;”;

Notice the Oracle OLE database driver in the above code. This tells your code exactly what driver to use, so you know your code will be able to parse and communicate with the database data.

Get some SQL Server 2012 training with a class at Udemy.com.

Microsoft Access is a common database format used by small companies. Access is only used for small amounts of data or your application will have poor performance. It’s also less secure than using a real database server machine. However, some small applications use a Microsoft Access database to store basic information. Since Access uses a file, you must specify the Access driver type in the connection string and the location of the file. The file can be located on the local computer or in a shared folder. The following code is an example of a connection string that connects to a Microsoft Access database:

string connString = “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\data\filename.accdb;Persist Security Info=False; Password=password”;

In the above example, the OLE Access database driver is used. The file is located in the “Data” folder on the local C drive and security does not persist, so you’ll need to log in each time you need to use the file. The password is set in the file. Even though you run a small office and don’t think you need a database password, you should always use a password on a database file that contains sensitive company data.

These are just a few connection strings, but these are the main strings you’ll need for most major Microsoft environment web server connections. You’ll need to pass one connection string to the connection object when you write your website code. It might take a few attempts before you figure out the right connection string. When you set up your string in a Microsoft project, you typically place the connection string in the web.config or app.config file.