Version:

LESSON

Connecting to SQL Server with Windows Login

Description

Learn about the files and configuration changes that are required to connect to Microsoft SQL Server using Windows credentials.

Video recorded using: Ignition 8.1

Transcript

(open in window)

[00:00] In this lesson, I'll demonstrate how to connect to a Microsoft SQL Server database using Windows authentication instead of SQL authentication. In my setup, I already have SQL Server Express installed on the same machine I have Ignition installed on. The first thing you'll need to do is import a DLL file that will allow you to use Windows authentication with SQL Server. You can get this DLL file by downloading the JDBC driver from Microsoft's website. I'll include a link below the video to the JDBC support matrix that's currently on the screen. You can figure out which version of the JRE is used for your version of Ignition by navigating to the Ignition install directory and then going into the user-lib and then the jdbc folders. Here's my MSSQL JDBC driver and I can see that it's for version 9.4.0 and it's for JRE11. If I go back to the support matrix and click on 9.4, I can find the version I need and I'll download the ZIP file. Once that's downloaded, extract the files and find the auth folder. Open the folder that's relevant for your OS version.

[01:07] Since I'm running 64-bit Windows, I'll open the x64 folder and in here will be the DLL file. Copy the DLL and then open up your Ignition install directory again and copy the DLL into the lib\core\gateway folder. After we have the DLL file, we'll need to tell Ignition to log on using the Windows account that we're trying to authenticate to SQL Server with. To do this, open up Services, which you can get to from the search bar or you can find it in the Control Panel Administrative tools. Scroll down till you see the Ignition service and right click on it and click Properties. Click the Log On tab and select the "This account" radio button. Once you've provided the Windows username and password, click OK. Then you'll need to restart the Ignition service. Note that this will temporarily bring the gateway down. Before we can create the connection we need to do one last thing.

[02:05] Open up the SQL Server Configuration Manager. If you followed along with our installation video, your SQL Server Browser service should be set to automatically start up, but just in case it isn't started or it's not set to automatic, you can change that here. Expand the SQL Server Network Configuration node and open the protocols. You'll need to change the TCP/IP protocol to enabled. It'll tell you that this won't take effect until the service is restarted. The easiest way to do that from here is to go back to SQL Server Services. Find your instance name, mine is SQLEXPRESS, and right click and restart. Now we can create the actual database connection from Ignition. Go to the gateway webpage's Config page and then go down to Database Connections. Create a new database connection with the Microsoft SQL Server JDBC driver, and then this will look pretty similar to creating a connection using SQL authentication. You can give the connection a name, point this to your database host, which is localhost for me, and pass the instance name.

[03:19] The main difference is that we're gonna leave the username and password fields blank, and then in the extra connection properties we'll add a new property called Integrated Security that we'll set to true. With that, we can create the connection and it should eventually say that it's valid, and now we have a Microsoft SQL Server connection that's set up to use Windows authentication.

You are editing this transcript.

Make any corrections to improve this transcript. We'll review any changes before posting them.