Forum Discussion
Cannot add SQL SSMS to On-Prem Gateway
Hi awg201 Usually when I get that error it is a credential issue. The password expired or needs to be updated.
Best would be to use a Service Account or if you then have an Admin account as mentioned where the password doesn't expire often. Otherwise you will have to update the connection regularly.
Firstly I would test connecting to the sql server in SSMS using the admin account. Run SSMS as the Admin user and connect as you would with your account.
https://woshub.com/run-program-as-different-user-windows/If that works. Create the gateway connection.
When creating the connection make sure you use Windows authentication and user the domain and username.
Hi awg201 Usually when I get that error it is a credential issue. The password expired or needs to be updated.
Best would be to use a Service Account or if you then have an Admin account as mentioned where the password doesn't expire often. Otherwise you will have to update the connection regularly.
Firstly I would test connecting to the sql server in SSMS using the admin account. Run SSMS as the Admin user and connect as you would with your account.
https://woshub.com/run-program-as-different-user-windows/
If that works. Create the gateway connection.
When creating the connection make sure you use Windows authentication and user the domain and username.
This was my suspicion and was right. The gateway is running under an admin account, so when using windows credential, it had failed because that account didn't have access to the database.
After adding db_reader pemissions in SSMS, i was able to add the connection without errors... only took my IT department a month to finally answer my ticket... 🙄