[00:00] In this lesson, we will take a look at how to connect ignition to Microsoft SQL server. Now, before I attempt to create a new database connection in Ignition, there are a couple of configuration changes that I need to make in SQL server. To do so, I'll need to open up the SQL server configuration manager in Windows. You can just click on the start menu and start typing SQL Server configuration manager and this should appear in the list. Once you have this open, the first area you're going to want to take a look at is the SQL server services and when you're here you will want to make sure that the SQL server browser is up and running. This mechanism allows Ignition to connect to SQL server by translating the database instance name to a TCP/IP Port Ignition can connect to. So if it says running, you're in a good state. If not, then we need to actually make sure it's running. This can be done by right clicking and telling it to start.

[01:06] In a production environment, you're most likely going to want this SQL server browser to start up a automatically whenever the computer starts. This can be done by making sure the start mode says automatic. If yours does not have an automatic start, you can come down on this right click menu here, select properties, and on this window here, come over to the service tab. Here you can find the start mode and change it to automatic. Then you go ahead and click okay, I'm just going to X out, since mine's already set up. With the running browser, we'll want to enable TCP IP on our database so Ignition can connect to it. To enable this property, you will come down to this SQL server network configuration section here and there should be a protocols for and some instance name. This will be the instance name you chose when installing SQL server. I didn't change mine, so I am using the default instance name, SQLEXPRESS. Double clicking on the protocols will open up the TCP IP settings.

[02:06] By default, TCP/IP is set to disabled, so all you have to do is right click or double click on it and set the enabled property to yes. When making a change to this property, you will see a message stating to restart SQL server for the change to take effect. A simple way to restart the SQL server is from this same window you would navigate back to the SQL server services and right click on your SQL server instance. This will provide the restart option. Once you've restarted, you get the TCP/IP enabled. A quick mention as we are here on the services screen, you don't need SQL server agent to run, this can be stopped. That's all we have to do from here, so I'm going to minimize this configuration manager window. Let's go ahead and create the database connection. I'm here on my gateway and I'm in the database connections page. Within the config tab, I'm going to create a new database connection by clicking on this link here.

[03:04] I'll select the Microsoft SQL server, JDBC driver and I'll click next. Let's go ahead and give this new connection a name. I'll just call mine SQL server. We need to specify a connect URL property to tell ignition where the database is. Now I have Ignition and SQL server installed on the same computer, so I can leave the host name parameter here alone. I could type in an IP address or a host name, but local host works just fine. With SQL server's, JDBC driver, we actually have an option for the rest of the parameters here, so I could either use the instance name or I could use the port instead. If you wanted to use the port, all you have to do is select the default instance name and backslash that's listed on the property here and delete or backspace. In most cases it's port 1433 unless we change it during the installation process. Once you're done, it should look something like this. Now I actually want to use my instance name here, so I'm going to delete the colon and the port number.

[04:06] I'll type backslash and my instance name. As you saw earlier, it was SQLEXPRESS all caps just like this. That's all we have to do for the Connect URL property. Now down a little bit more, we can specify a username and password. With SQL server, we have a few authentication methods. The simplest option is passing a username and password in here. This is called SQL authentication. We are going to go ahead and authenticate directly against the database. We simply type in the user that's configured into the database, so in my case I'm going to use SA and their password. There's also Windows authentication. Windows authentication is usually preferable and more secure. This basically means that the username and password used to connect comes from ignition's Windows service log on. This typically involves setting the ignition service to run as a certain user instead of a system.

[05:04] To use Windows authentication, you actually don't have to type in the username and password. Instead, what you do is come down to this extra connections property section. On this property you would leave the database name alone and place a semicolon at the end. I'm going to paste the rest in, but you would then type in integrated security equals true, spelled exactly like this capitalization and all. Now there is actually a little more to Windows authentication. There is an additional DLL file that you need to download and place into ignitions installation directory. For more details, take a look at the connecting to SQL server page. In the Ignition user manual. I have added a link to this user manual below the video. Now, like I said, I'm going to use SQL server authentication, so I'm going to go ahead and remove this extra line here. I'm going to to leave it just as the database name and this specifies which of the databases within SQL server this connection should connect to.

[06:05] We'll finish creating the connection by scrolling all the way down and clicking the create new database connection button and that's it. I got a valid status, so i'm good. Now of course, if you're seeing a fault message at all, take a look at this database connection status page. Here, you'll find any errors returned by the database as a result of the configuration we just attempted and that should be a pretty good start for troubleshooting.