You can help by commenting or suggesting your edit directly into the transcript. We'll review any changes before posting them. All comments are completely anonymous. For any comments that need a reply, consider emailing firstname.lastname@example.org.
We are experiencing playback issues from our video hosting provider. Please check back shortly.
This lesson is part of the Databases in Ignition course. You can browse the rest of the lessons below.
6:39Connecting to MySQL
5:56Connecting to Microsoft SQL Server Express
5:11Creating a SQLite Connection
Take topic challenge
Take topic challenge
Connecting to Microsoft SQL Server Express
Learn about what configuration settings and connection methods to use when connecting to a Microsoft SQL (MSSQL) Server Express database.
Video recorded using: Ignition 7.9
Transcript(open in window)
[00:00] In this lesson, we'll take a look at how to connect Ignition to 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. So the first thing you're going to want to do is you're going to want 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're going to want to make sure that the SQL Server Browser is up and running. This is basically one of the mechanisms that allows Ignition to connect to SQL Server. So if it says it's running, you're in a good state. If not, then we need to actually make sure it's running. So you can actually just right-click and tell it to start. Now in a production environment, you're most likely going to want this SQL Server Browser to start up automatically whenever the computer starts. So you're going to want to make sure the Start Mode says Automatic. So if yours doesn't, what you can do is you can come down on this right-click menu here, select Properties, and on this window here, come over to the Service tab, find the Start Mode and change it to Automatic. And then you go ahead and click OK. I'm just going to X out since mine's already set up. And the next thing you're going to want to do is you want to come down to this SQL Server Network Configuration section here and there should be a "Protocols for" and then some instance name. What ever instance name you chose when you're installing SQL Server, that's what will be listed here. I didn't change mine so I am using SQLEXPRESS so this is the default instance name for SQLEXPRESS. You want to make sure that when this is selected, this TCPIP Protocol name is Enabled. By default, it's set to Disabled so all you have to do is you can just double-click or you can right-click and Enable. Now, if you make a change to this, you're going to see a little message appear here saying that you need to restart SQL Server for the change to take effect. So that's easy enough to do. You can restart SQL Server however you like. The easiest way is probably just from this same window. If you head back to SQL Server Services, this guy here, when you see that's my instance name here, The SQL Server Item. This is my SQL Server Instance so I could actually just right-click and Restart here if I need to. But once you've restarted that, you get the TCPIP enabled, you have both of these services running, you don't need SQL Server Agent to run, that can be stopped. That's all we have to do from here, so I'm going to minimize this Configuration Manager window. And let's go ahead and create the database connection. So I'm back here on my Gateway, and I'm in the CONFIGURE section. I'm going to scroll down just a little bit. You can see that I'm under DATABASES and Connections. I'm going to create a new database connection by clicking on this link here. We'll scroll down the list of drivers here. I'll select the Microsoft SQLServer JDBC Driver and I'll click Next. Let's go ahead and give this new connection a name here. So I'll just call mine SQLServer just like that. And scroll down a little bit here. We need to go ahead and specify a Connect URL so we got to tell Ignition where the database is. Now I have Ignition and SQLServer 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 localhost works just fine. And with SQLServer'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 slash that's listed on the property here, hit delete or backspace, whichever you prefer, SQL Server's running on. 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 go ahead and just select that colon. I'm going to select the 1433 there. Hit Delete. And I'll type backslash and my instance name as you saw earlier it was SQLEXPRESS, all caps, just like this. So that's all we have to do for the connect URL. Now, we scroll down a little more here and we specify a user name and password because we are using SQL Server, we actually have an option here, so. The simplest option, what I'm going to do in this video here is, involves using a, passing a user name and a password in here. So this is called SQL Authentication. So we are going to go ahead and authenticate directly against the database. We just type in the user that's configured into the database, so in my case, I'm going to use s-a and their password. So this would be SQL Authentication. There's also Windows Authentication. Window's Authentication is usually preferable and more secure. This basically means that the user name and password used to the 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. To use Windows Authentication, you actually don't have to type anything in here. You don't have to type in the user name and password at all. Instead, what you do is you come down to this Extra Connections Properties section. Leave the database name alone. Just put uh, come over here to the end, place a semi-colon down. And I'm going to paste this in, but you just want to type this out, "integratedSecurity=true" spelled just 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 Ignition's installation directory. For more details, take a look at that "Connecting to SQL Server" page in the Ignition User Manual. 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 leave it just as the database name and as you can probably tell, this little, under Extra Connection Properties here, this database name specifies which of the databases within SQL Server this connection should connect to. So, I'm not going to make any other changes. I'm going to scroll on down, all the way to the bottom of this page and we can click this Create New Database Connection button. And that's it. I got to a valid status, so I'm good. We're done. 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.