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?
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]
Here is where it comes in on the gateway (Connect URL):
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.
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.
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.
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)
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.
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).
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?
