Knowledgebase:

Microsoft SQL Server (MSSQL) Connection Guide for Ignition

Posted by Dave Fogle, Last modified by Anna Christian on 06/14/2018 03:50 PM

Microsoft SQL Server is a popular robust relational database produced by Microsoft. Ignition can connect to Microsoft SQL Server, however many users find difficulty in getting all of the settings and parameters correct. There are several different ways you can connect to Microsoft SQL Server (all using TCP/IP communication):

  • Specifying a Port using Windows Authentication
  • Specifying an Instance Name using Windows Authentication
  • Specifying a Port using SQL Authentication
  • Specifying an Instance Name using SQL Authentication

The most common method, the one out of the box, is to connect using an Instance Name and Windows Authentication.

Background

Before we begin, let's talk a little about how instances work. Microsoft SQL Server supports multiple instances of the database running concurrently on the same computer. Each instance has its own name and set of system and user databases that are not shared between instances. Applications, such as Ignition, can connect to each instance on a computer in much the same way they connect to databases running on different computers. Each instance gets assigned a dynamic TCP/IP port on startup that it will listen on for any incoming requests. Since the port is dynamic and the application will not know what the new port is, it must connect using the instance name.

If the communication is over TCP/IP and the application knows the instance name, how will the application find which port to communicate over?

The answer is the Microsoft SQL Server Browser Service. The Microsoft SQL Server Browser program runs as a Windows service and listens for all incoming requests for resources and provides information, such as the TCP/IP port, about each instance installed on the computer. Microsoft SQL Server Browser also contributes to the following actions: 

  • Browsing a list of available servers
  • Connecting to the correct server instance

If the Microsoft SQL Server Browser service is not running, you are still able to connect to SQL Server if you provide the correct port number. For instance, you can connect to the default instance of SQL Server with TCP/IP if it is running on port 1433. 

Check 1: Make Sure the Database has TCP/IP Enabled

Ignition connects using TCP/IP, so the first step is to make sure your database has TCP/IP enabled. To check: 

  1. Open up the SQL Server Configuration Manager from Start > All Programs > Microsoft SQL Server Version # > Configuration Tools > SQL Server Configuration Manager.
  2. Once it is open, you will see all of the instances setup on that machine by expanding "SQL Server Version # Network Configuration".
  3. Find the database (or instance) you plan on using and click on it.
  4. To the right you will see all of the protocols the database supports. One of the protocols is TCP/IP. Make sure the status next to TCP/IP is set to Enabled. If not, double click on "TCP/IP" and choose Yes from the drop-down next to Enabled and press OK.

    SQL Server Configuration Manager

    TCP/IP Properties

Check 2: Make Sure Microsoft SQL Server Browser is Running

If you ARE connecting to your database using a NAMED INSTANCE you must make sure that the Microsoft SQL Server Browser is running. As mentioned earlier, the Microsoft SQL Server Browser translates the instance name to a TCP/IP port in order for your application (Ignition) to connect to it. To check: 

  1. Open up the SQL Server Configuration Manager from Start > All Programs > Microsoft SQL Server Version # > Configuration Tools > SQL Server Configuration Manager.
  2. Once it is open, select the "SQL Server Version # Services" section.
  3. To the right you will see all of the services installed. One of the services is the "SQL Server Browser". Make sure this service is in fact running. If the service is not running, right click and select Start.

    SQL Server Browser

    Note: The service could be disabled so you may have to double click on it to enable the service before starting it up.

Connection Scenarios

Scenario 1: Connecting Using Instance Name and SQL Authentication

Since we are using SQL authentication, Microsoft SQL Server must allow this type of authentication. By default, Microsoft SQL Server only allows Windows authentication since it is more secure.

To enable SQL authentication:

  1. Open the SQL Server Management Studio from Start > All Programs > Microsoft SQL Server Version # > SQL Server Management Studio.
  2. Once open and connected to your database, right click on the top level node  (instance name)  in the Object Explorer, and select Properties.
  3. Select Security on the left hand side.
  4. Verify that "SQL Server and Windows Authentication" mode is selected. If not, select it and press OK.
  5. You will have to restart the "SQL Server Windows" service for this setting to take effect. Open the "SQL Server Configuration Manager" (from previous steps) and restart the "SQL Server (Instance Name)" item from the "SQL Server Services" section.

    SQL Authentication
     

 Now that Microsoft SQL Server accepts SQL authentication we can move to configuring Ignition. Follow these steps:

  1. Open and login into the Ignition Gateway configuration page from your web-browser. (http://hostname:8088/main/web/config)
  2. Select Databases > Connections from the menu.
  3. Click "Create new Database Connection"
  4. Select the "Microsoft SQL Server JDBC Driver" and press next.
  5. Give the connection a name like "SQL Server SQL Auth"
  6. Set the "Connect URL" to: jdbc:sqlserver://Hostname\InstanceName
    Replace the "Hostname" with your databases IP address or hostname and replace the "InstanceName" with your databases instance name. Here are a couple of examples:
    jdbc:sqlserver://localhost\SQLEXPRESS
    jdbc:sqlserver://10.10.1.5\MSSQLSERVER
  7. Set the username and password to a valid SQL authentication user.
    For example, "sa" is an administrator account you can use.
    To add your own user account open the "SQL Server Management Studio", expand the Security > Logins folder. There you can see all of the current logins.
    Right click on the Logins folder and click "New Login...". Choose the SQL Server authentication mode and type in a username and password. 
    Note: You will also have to add permissions to your database by mapping "db_datareader" and "db_datawriter" to the new user in the "User Mapping" section.
  8. Lastly, set the "Extra Connection Properties" to your database.
    For example: databaseName=test (Replace "test" with your database name.)
  9. Click the "Create New Database Connection" button at the bottom of the page, and the status should be Valid after a couple of seconds.

 If the connection is "Faulted" click on the "Database Connection Status" link to find out why. Typically the username/password is incorrect or the user doesn't have the right permissions.

Scenario 2: Connecting Using Instance Name and Windows Authentication

In Windows authentication mode, the username and password used to connect comes from the Ignition Windows Service logon. By default, the Ignition Windows Service is set to LOCAL SYSTEM account, which usually doesn't have privileges to connect.

To connect using Windows authentication:

  1. First we have to download the necessary microsoft .dll files. We have made these available here.
  2. Extract the files to your desktop. Locate the sqljdbc_auth.dll from the correct architecture folder (x86 for 32-bit and x64 for 64-bit)
  3. Copy the sqljdbc_auth.dll file to the following location: C:\Program Files\Inductive Automation\Ignition\lib\
  4. Setup Ignition to logon using the right Windows account:
    1. Open the "Services Control Panel" from Start > Control Panel > Administrative Tools > Services.
    2. Right click on the "Ignition" service and choose "Properties".
    3. Select the "Log On" tab.
    4. Choose "This Account" and enter in your Windows username and password. Press OK to save.
    5. Restart the Ignition service by either clicking the restart button in the toolbar or stopping and starting from the right click menu.

Now we can move to configuring the database connection in Ignition. Follow these steps:

  1. Open and login into the Ignition Gateway configuration page from your web-browser. (http://hostname:8088/main/web/config)
  2. Select Databases > Connections from the menu.
  3. Click "Create new Database Connection"
  4. Select the "Microsoft SQL Server JDBC Driver" and press next.
  5. Give the connection a name like "SQL Server Windows Auth"
  6. Set the "Connect URL" to: jdbc:sqlserver://Hostname\InstanceName
    Replace the "Hostname" with your databases IP address or hostname and replace the "InstanceName" with your databases instance name. Here are a couple of examples:
    jdbc:sqlserver://localhost\SQLEXPRESS
    jdbc:sqlserver://10.10.1.5\MSSQLSERVER
  7. Leave the username and password blank.
  8. Lastly, set the "Extra Connection Properties" to your database name, and set it to use "Integrated Security". For example: 
    databaseName=test; integratedSecurity=true;
    Replace "test" with your database name.
  9. Press "Create New Database Connection" and the status should be Valid after a couple of seconds.

 Again, if the connection is "Faulted" click on the "Database Connection Status" link to find out why.

 Scenario 3: Connecting Using Port and SQL Authentication

Connecting using a port and SQL authentication is just like Scenario 1 above, except we specify a port instead of the instance name. Follow the steps for Scenario 1, but  bet the "Connect URL" in step 6 to:
jdbc:sqlserver://Hostname:Port
Replace the "Hostname" with your databases IP address or hostname and replace the "Port" with your databases TCP/IP port. Here are a couple of examples:
jdbc:sqlserver://localhost:1433
jdbc:sqlserver://10.10.1.5:1433

 Scenario 4: Connecting Using Port and Windows Authentication

Connecting using a port and Windows authentication is just like Scenario 2 above, except we specify a port instead of the instance name.  Follow the steps for Scenario 1, but set the "Connect URL" in step 6 to:
jdbc:sqlserver://Hostname:Port
Replace the "Hostname" with your databases IP address or hostname and replace the "Port" with your databases TCP/IP port. Here are a couple of examples:
jdbc:sqlserver://localhost:1433
jdbc:sqlserver://10.10.1.5:1433

(25 vote(s))
Helpful
Not helpful