Skip to main content
Version: 7.9

Connecting to Microsoft SQL Server Express

Inductive University

Connecting to Microsoft SQL Server Express

Watch the video

Overview​

This page details how to configure a connection to a SQL Server database. Note that in some cases you may need to update your JDBC driver, so it is a good idea to consult the table of supported databases.

Connecting to Microsoft SQL Server​

  1. From the Gateway, go to the Configure section.
  2. From the menu on the left, select Databases > Connections.
    The Database Connections page will be displayed.
  3. Look for the orange arrow, click on Create new Database Connection....
  4. Select Microsoft SQLServer JDBC Driver and click Next.

" "

The New Database Connection page will be displayed.

  1. On the New Database Connection page, enter the following information:
    Name: SQLServer
    Connect URL: jdbc:sqlserver://localhost\SQLEXPRESS
    username: sa
    password: sqlserver (password is what you entered during the SQL Server installation. For this example, password is sqlserver)
note

We are connecting to the express edition of SQL Server using the default instance name in the Connect URL field. If you have the full SQL Server with default settings, replace SQLEXPRESS with MSSQLSERVER.

" "

  1. At the bottom of the form, click on Create New Database Connection.
    Your connection is now created and the Database Connections page is displayed showing the Status of your connection as Valid.

" "

  1. 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 is Enabled​

Ignition connects using TCP/IP, therefore make sure your database has TCP/IP enabled.

To check if TCP/IP is 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 will be displayed.
  2. To see all the instances setup 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.

" "

  1. 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.

To check if Microsoft SQL Server Browser is running​

Open the SQL Server Configuration Manager from Start > All Programs > Microsoft SQL Server Version # > Configuration Tools > SQL Server Configuration Manager

Select the SQL Server Services section.

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.

" "

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.

To 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.

" "

  1. From the Server Properties window, on the left side, select Security.

" "

  1. 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.
  2. Open the SQL Server Configuration Manager at Start > All Programs > Microsoft SQL Server Version # > Configuration Tools > SQL Server Configuration Manager.
  3. 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.

To Configure the database connection in Ignition​

  1. Go to and login to the Ignition Gateway configuration 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

  1. Set the username and password to a valid SQL authentication user. For example, sa is the default administrator account you can use.
  2. 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.
  • To add a new login, right-click on the Logins folder and click New Login....
    The Login window is displayed.
  • 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.

  1. 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)
  2. 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.

To set up the service to use Windows Authentication​

  1. You must install the Microsoft JDBC driver package (version 3) before attempting to connect using Windows Authentication. You can download the file here.
  2. Extract the files to your desktop. Locate the appropriate DLL from the correct architecture folder (x86 for 32-bit JDBC and x64 for 64-bit JDBC) inside of the enu/auth folders in the zip file. Make sure to select the version that matches your database connector, not your computer architecture.
  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.

  1. The account used to connect will be the account that Ignition is running under in the services menu. To setup Ignition to logon using the right Windows account, open the Services Control Panel from Start > Control Panel > Administrative Tools > Services
  2. Right-click the Ignition service and choose Properties.
  3. Select the Log On tab.
  4. Choose the This account radio button and enter in your Windows username and password.
  5. Click OK to save.
  6. 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).

To configure the database connection in Ignition​

  1. Go to and login to the Ignition Gateway configuration 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)

  1. 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​

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.

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.

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

Last updated on by ia-sshamgar