Ignition 8.1 MSSQL Express database connection issues

I am having trouble connecting to my new blank sql database through the ignition gateway. I receive this error : Cannot create PoolableConnectionFactory (Login failed for user 'ignition'. ClientConnectionId:06f66d20-db57-4c34-b7a9-29a9146fc73a)

I first attempted to setup the gateway using the default sa user on MSSQL but after having the issues I created and new user named "ignition" but the problem persist. The password is correct and I am wondering if there is a setting in the SQL database that may be wrong. Does anyone have any experience with this?

Are you able to connect to it through SSMS?

One thing I can think of is that when you use MS SQL Express, the service name is not MSSQLSERVER - it's something like SQLEXPRESS (I can't remember exactly).

[Windows Services]
image

Here is where it comes in on the gateway (Connect URL):

Also, if the database is truly empty, you may want to leave the Extra Connection Properties field blank until there is a database.

Also make sure you are not using LocalDB, and that you are conecting over TCP/IP

Make sure in SQL Server Configuration Manager that under SQL Server Network Configuration/Protocols for (INSTANCE NAME) that TCP/IP is enabled. Also check under other tree sections that under client protocols that TCP/IP is enabled, but I think the server config is the most critical of those.

1 Like



I have attached snapshots of my sample database as well as the ignition config. The socket connection seems to be made but the error persist. I am wondering if my "name" field has the wrong info in it?

You should also plan on regretting your choice of SQL Express.

You will run out of space if trying to use this for production. You will suffer long-term performance problems if running this on the same server as the Ignition gateway. If you need a dev environment compatible with SQL Server, just use Microsoft's actual SQL Server, the free Developer edition (on a separate machine). If you need something free, use PostgreSQL or MariaDB.

Really.

2 Likes

No, the Name field should be fine.

When you created the ignition user, did you assign the appropriate permissions so that that user can access the CBTProduction database? Also, as other folks pointed out, if you enable TCP/IP (which you must), you also will need to restart the SQLEXPRESS services. I don't know if you are able to do that / have already done it. If its a local DB, that's easy - if not, it can take some coordination.

Here is how-to video from IU: link.

1 Like

You can't use the developer edition for production though, so unless this is a development system, stick with SQL Standard at a minimum for production (if you're required to use MS SQL, otherwise, there's better free options)

Yes my ignition user has the same permissions as the sa user. My TCP/IP is also currently enabled and running.

Yes this is just an in house proof of concept test which is why I went with SQL Express. If/when we decide to go live, we will be using full version.

You can always try using the SA user temporarily, but it looks like you may have not enabled mixed mode (sa account is disabled). You'll need to enable mixed mode for the logins to work.

image

After you enabled it, did you restart the services? That's a key part of this as well. This will have to be done on the machine that SQL Server Express is installed. Hopefully this is a dev box on your local machine.

Go to Start Menu -> Services -> SQL Server [Express] -> Right-click -> Restart (or Stop/Start).
image

Yes, I restarted after and have verified that my SQL server is "Running" and my SQL server browser is "Running". I did notice my SQL server agent is "Stopped" is that an issue?

This was the issue. Thank you!