Q100066: [Connections] SQL Server

The SQL Server Connection is a definition which tells a module, how to connect to and communicate with a specific Microsoft SQL Server database. Connections can either be entered directly into the parameter in the module, or can be managed through the global connections tool. If you will be using the same connection in different modules, it is much easier to manage the connection information by creating a single global connection. Whether you use a locally defined value in each SQL Server module, or use the global definitions for your SQL Server modules, the format of the connection value is the same for both.

If you use the global connection manager, you will see that as soon as you create a new entry, it will provide you a template for starting your SQL Server Connection definition. It would look like the example below.

Server=<servername>;Database=<database>;User=<username>;Password=<password>

Here we see that there are multiple values specified in this single parameter. Each value is a single name-value pair. You should notice that each of the name-value entries consists of a name on one side, followed by an equals sign (=), followed by the value, and finally terminated by a semicolon. Thus if we breakdown the example above, the list of name-value pairs might appear a little more legible if we look at them this way.

Server=<servername>;
Database=<database>;
User=<username>;
Password=<password>

Formatted this way, it is much easier to read. The name is on the left of the equals sign, while the value is on the right. Where each value is, you would edit it to use the value which is specific for your installation. If you had to specify your server by IP Address, you would edit yours to look something like the following example.

Server=10.25.25.7;Database=MySqlSvrDatabaseName;User=MySqlSvrLogin;Password=MySqlSvrPassword

If the same server had a fully qualified domain name, then your SQL Server Connection might look more like this example.

Server=sqldb2.myaccount.mycloudprovider.com;Database=MySqlSvrCloudDatabase;User=MyCloudAccount;Password=MyCloudPassword

If the same server was part of the same domain that your JobServer.NET service is running on, then you might also be able to use the server network name on your domain.

Server=CORPDB2;Database=Warehouse;User=WarehouseLogin;Password=WarehousePassword

Note that this connection information aligns exactly with the .NET SqlClient Connection properties. Thus, you are able to provide additional properties that are supported by SqlClient, such as a few of the examples in the table below.

Note

If you need to diagnose connectivity issues with SQL Server, it is highly recommended to use sqlcmd.exe on the command line to validate your connectivity issues. If you do not already have the sqlcmd.exe utility installed, it is included as part of the more comprehensive Microsoft SQL Server Management Studio, or SSMS. The sqlcmd.exe utility is also available for download separately from the Microsoft website with the Microsoft Command Line Utilities for SQL Server.

To use sqlcmd.exe to test your connection with your SQL Server database, open a command line window and try it using your own settings as shown in the example below. Note that this is case sensitive and and commandline switches below use both upper and lowercase values, as well as the password value is case sensitive. The example below, when supplied with all the correct values with your SQL Server, should return the version information about your SQL Server installation. If there are any errors, then the connection information is either incorrect, or you are having a communications issue between your machine and your SQL Server.

C:\>sqlcmd -S <servername> -d <database> -U <username> -P <password> -Q "select @@version"