Connecting to Microsoft SQL Server

Connect to Microsoft SQL Server

1. On the Gateway Webpage, go to the Config section.
   
2. Scroll down to Databases > Connections.
   
   
     
3.  The Database Connections page is displayed. Click on Create new Database Connection....
4. Select Microsoft SQLServer JDBC Driver, and click Next.
   
   
   
   

5. On the Database Connections page, enter the following information:
   Name: SQLServer
   Connect URL: jdbc:sqlserver://localhost\SQLEXPRESS

   Note: We are connecting to the express edition of SQL Server using the default instance name. If you have the full SQL Server with default settings, replace SQLEXPRESS with the instance name of your SQL Server installation. Non-express versions of SQL Server tend to use MSSQLSERVER as a default instance name. 
   username: sa
   password: sqlserver (password is what you entered during the SQL Server installation. For this example, password is sqlserver)
   
   
   
    
6. At the bottom of the form, click on Create New Database Connection. 
   Your connection is now created. The Database Connections page is displayed and will show the status of Reconnecting, then Valid.
   
   
   
   
7. To display the details about the status of your database connection, see the Note on the above window and click on the Database Connection Status link.
   
    

Microsoft SQL Server Connection Guide

This guide helps you with any difficulties you may have in getting the correct settings and parameters when connecting Ignition to Microsoft SQL Server, a popular and robust relational database.

Multiple Instances of Database

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. By default, each instance gets assigned a dynamic TCP/IP port on startup that listens for any incoming requests. Since the port is dynamic and the application does not know what the new port is, it must connect using the instance name. 

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

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 these two actions: browsing a list of available servers and connecting to the correct server instance.

If the Microsoft SQL Server Browser service is not running, you can still connect to SQL Server if you provide the correct port number. For example, 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, therefore make sure your database has TCP/IP enabled.

1. Open the SQL Server Configuration Manager from Start > All Programs > Microsoft SQL Server Version # > Configuration Tools > SQL Server Configuration Manager.
   The Sql Server Configuration Manager window is displayed.
    
2. To see all the instances set up on that machine, expand SQL Server Network Configuration.
   
   
3. Find the database (or instance) you plan on using. To the right, all of the protocols the database supports are shown. Find the TCP/IP protocol and select it.
   
   
    
4. Make sure the Status next to TCP/IP is Enabled. If not, double-click TCP/IP and choose Yes from the drop-down next to Enabled and click OK.
   
    

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 Ignition to connect to it.

1. Open the SQL Server Configuration Manager from Start > All Programs > Microsoft SQL Server Version # > Configuration Tools > SQL Server Configuration Manager.
   
   
2. Select the SQL Server Services section.
   
   
3. On the right, see all of the services installed. One of the services is SQL Server Browser. Make sure this service is in fact running. If the service is not running, right-click and select Start.
   
   Note: The service could be disabled, so you may need to double-click it to enable the service before starting it up.
   
   
    

Check 3: Make Sure There is a Database Created

If you are connecting to an existing SQL Server installation, you only need to know the name of the database and you can skip this step. In newer versions of SQL Server, a fresh installation does not include a database so you must first create a new database. 

1. Open the SQL Server Management Studio program. This program was an option when you installed SQL Server.
   
   
2. Log into the Management Studio using either SQL Server Authentication or Windows Authentication.
   
   
3. In the Object Explorer (on the left), expand the instance folder to find the Databases folder. The instances folder is usually expanded by default.
   
   
4. Right Click on the Databases folder and select the New Database... option.
   
   
5. Type in a Database Name. 
   
   
6. Click the OK button in the lower right. You can then expand the Databases folder in the Object Explorer to see the new database. If it doesn't show up right away you can right-click to refresh.
   
   Security - Make your user a db_owner of the new database.
7. In the Object Explorer, expand to the Security > Logins folder, right click on your username, and select Properties.
   
   
8. In the User Mapping page click the checkbox next to your new database, then click the db_owner checkbox below.
   
   
9. Click OK. Now your user has access to a database.

Different Ways of Connecting to SQL Server

Now that you have ensured that TCP/IP is enables and the Microsoft SQL Server Browser is running, you can connect to Microsoft SQL Server in four different ways (all using TCP/IP communication) as follows:

1. Connect using an Instance Name and SQL Authentication.
    
2. Connect using an Instance Name and Windows Authentication (this is the most common method).
    
3. Connect using a Port and SQL Authentication.
    
4. Connect using a Port and Windows Authentication.

Scenario 1: Connect By Using an Instance Name and SQL Authentication

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

Enable SQL Authentication

1. Open the Microsoft SQL Server Management Studio window from Start > All Programs > Microsoft SQL Server Version # > SQL Server Management Studio.
   The window is displayed showing connections to your database.
    
2. Right-click the top-level database in the Object Explorer and select Properties.
    
   
    
3. From the Server Properties window, on the left side, select Security.
   
   
    
4. Verify that SQL Server and Windows Authentication mode is selected. 
   If not, select it and click OK. 
   
   Now you need to restart the SQL Server Windows service so that this setting takes effect.
    
5. Open the SQL Server Configuration Manager at 
   Start > All Programs > Microsoft SQL Server Version # > Configuration Tools > SQL Server Configuration Manager.
   
   
6. Select the SQL Server Services section and restart the SQL Server (Instance Name) item.
   
   
    
   Now that Microsoft SQL Server accepts SQL authentication, we can configure Ignition.

Configure the Database Connection in Ignition

1. Go to and login to the Ignition Gateway Config page from your webbrowser at http://hostname:8088/main/web/config/
2. Select Databases > Connections from the menu.
3. Click on Create new Database Connection.
4. Select Microsoft SQL Server JDBC Driver and click Next.
   
   
    
5. In the New Database Connection window, enter the following information:
   
   Name: SQLServer_SQLAuth (no spaces)
   
   Connect URL: jdbc:sqlserver://Hostname\InstanceName
   
   where Hostname is your databases IP address or hostname and InstanceName is your databases instance name, for example:
   jdbc:sqlserver://localhost\SQLEXPRESS
   jdbc:sqlserver://10.10.1.5\MSSQLSERVER
    
6. Set the username and password to a valid SQL authentication user. For example, sa is the default administrator account you can use.
    
   
   
   
7. To add your own user account, open the SQL Server Management Studio and expand the Security > Logins folder.
   You will see all the current logins including sa and you can add a new login.
    
   1. To add a new login, right-click on the Logins folder and click New Login....
      The Login window is displayed.
       

   2. Choose the SQL Server authentication mode and type in a Login name 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 of the Login window. If you want Ignition to be able to create tables (ie: for Tag History), you also need to give table creation access such as db_owner.

8. Go back to the New Database Connection page in the Gateway, enter the name of your database, for example, in the Extra Connection Properties enter: databaseName=test (replace test with your database name, not the instance name).
    
9. Click Create New Database Connection.
   The Database Connection page is displayed showing the Status as 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: Connect By 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.

Set Up the Service to Use Windows Authentication

1. Download a copy of the SQL Server JDBC driver. Specifically, download a ZIP or tar.gz file (NOT an installer), as you will need to extract a specific file and relocate it to the gateway's installation directory. The exact version required depends on the version of Java your gateway is using. Ignition 8.0 uses uses Java 11, but future updates could change the java version.

    Although, it's recommended you use the most recent driver available for your system, make sure the version of the JDBC Driver Ignition is using matches the downloaded DLL file. Your current driver version can be seen in the following directory:

   {installDirectory}/user-lib/jdbc

2. Locate the DLL file from the correct architecture folder ("x64" for 64-bit JDBC) inside of the enu/auth folders in the zip file. 

3. Copy the DLL file to the lib folder in your install directory. If you have the default install directory, it's in the following location: 
   C:\Program Files\Inductive Automation\Ignition\lib\
   

   Note: Older versions of the JDBC driver (such as version 7.2.1) need to be renamed to sqljdbc_auth.dll  before Ignition can utilize the file. However, in more modern versions of the driver, this is no longer the case. 

4. The account used to connect will be the account that Ignition is running under in the services menu. To set up Ignition to logon using the right Windows account, open the Services Control Panel from Start > Control Panel > Administrative Tools > Services
5. Right-click the Ignition service (or whatever service name your Ignition installation is using) and choose Properties.
6. Select the Log On tab.
7. Choose the This account radio button and enter in your Windows username and password.
8. Click OK to save.
9. Now restart the Ignition service to make this change take effect. Click the Action > Restart button in the menubar to restart the Ignition service (or your can stop and start from the right-click menu).

Configure the Database Connection in Ignition

1. Go to and login to the Ignition Gateway Config page from your webbrowser at http://hostname:8088/main/web/config/ 
   
2. Select Databases > Connections from the menu.
3. Click on Create new Database Connection.
4. Select Microsoft SQL Server JDBC Driver and click Next.
   
   
   
   
5. On the New Database Connection page, enter the following information:
   Name: SQLServer_WinAuth (no spaces)
   
   Connect URL: jdbc:sqlserver://Hostname\InstanceName
   
   where Hostname is your databases IP address or hostname and InstanceName is your databases instance name, for example:
   jdbc:sqlserver://localhost\SQLEXPRESS
   jdbc:sqlserver://10.10.1.5\MSSQLSERVER
   
   username: leave blank
   password: leave blank
   
   Extra Connection Properties:
   databaseName=test; integratedSecurity=true; (replace test with your database name)
   
    
   
   
6. Click on Create New Database Connection.
   The Status should be Valid after a couple of seconds. Again, if the connection is Faulted, click the Database Connection Status link to find out why.

Scenario 3: Connect By Using Port and SQL Authentication

1. Connecting by using a port and SQL authentication is just like scenario 1 above except you specify a port instead of the instance name in the New Database Connection page.
2. Enter the following:
   
   Connect URL: jdbc:sqlserver://Hostname:Port
   
   where Hostname is your databases IP address or hostname and Port is your databases TCP/IP port (SQLSERVER default port is 1433), for example:
   jdbc:sqlserver://localhost:1433
   jdbc:sqlserver://10.10.1.5:1433

Scenario 4: Connect By Using Port and Windows Authentication

Connecting by using a port and Windows authentication is just like scenario 2 above except you specify a port instead of the instance name in the New Database Connection page. Don't forget to download the sqljdbc_auth.dll file if you need it.

Enter the following:

Connect URL: jdbc:sqlserver://Hostname:Port

where Hostname is your databases IP address or hostname and Port is your databases TCP/IP port (SQLSERVER default port is 1433), for example:
jdbc:sqlserver://localhost:1433
jdbc:sqlserver://10.10.1.5:1433

Troubleshooting

TCP/IP Communication Not Enabled

SQL Server requires that you explicitly turn on TCP connectivity. To do this, use the SQL Server Configuration Manager, located in the Start menu under Microsoft SQL Server > Configuration Tools. Under SQL Server Network Configuration, select your instance, and then enable TCP/IP in the panel to the right. You need to restart the server for the change to take affect.

Window Firewall

When connecting remotely, make sure that Windows Firewall is disabled, or set up to allow the necessary ports. Normally ports 1434 and 1433 must be open for TCP traffic, but other ports may be required based on configuration.

SQL Server Browser Process Not Running

To connect to a named instance, the SQL Server Browser service must be running. It is occasionally disabled by default, so you need to verify that the service is not only running, but set to
start automatically on bootup. The service can be found in the Windows Service Manager (Control Panel > Administrative Tools > Services).

Mixed Mode Authentication Not Enabled

Unless selected during setup, mixed mode or SQL authentication is not enabled by default. This mode of authentication is the username/password scheme that most users are used to. When not enabled, SQL Server only allows connections using Windows Authentication. Due to the ease of using SQL Authentication over Windows Authentication, we recommend enabling this option and defining a user account for Ignition.

1. To enable this, open the SQL Server Management Studio.
2. Connect to the server.
3. Right click on the instance and select Properties.
4. Under Security, select SQL Sever and Windows Authentication mode.

TLS/Security

An update starting in Java 8 disables TLS 1.0 and 1.1, which may affect your connection to an MSSQL database. The Support department has written a guide on how to resolve this issue.

JDBC Drivers and Translators

In some cases, you may need to add your own JDBC Driver, or configure a Translator. More information on configuring these can be found on the JDBC Drivers and Translators page. However, you may need to check the JDBC driver's documentation for information on how to configure them. 

If you are using a more recent JDBC driver such as 10.2 or 11.2, you will need to add the following argument to the extra connection properties: trustServerCertificate=true. This will allow the transport layer to use SSL to encrypt the channel and bypass going through the certificate chain to validate trust.