I'm trying to connect to a remote instance of SQLServer using a connection string for a Go program that I'm writing.
I have a local version of the remote database with the same users.
If I connect to my local DB with a connection string like this, it works just fine:
Data Source=localhost;Initial Catalog=master;User Id=<user>;Password=<password>;
Now if I use the same credentials, but I just change the data source, it also works just fine:
Data Source=<remoteIP>;Initial Catalog=master;User Id=<user>;Password=<password>;
Now if I try and log in using 'sa', it works locally, but not remotely.
This works just fine:
Data Source=localhost;Initial Catalog=master;User Id=sa;Password=<password>;
But this does not work:
Data Source=<remoteIP>;Initial Catalog=master;User Id=sa;Password=<password>;
The error I get when I try to connect to the remote server in my Go program is:
Login error: mssql: Login failed for user 'sa'.
The really frustrating part is that if I copy and paste the username and password, I can connect to the remote DB in SMSS using that login information, and everything works just fine.
I've double-checked to ensure that the remote 'sa' login allows for both Windows Authentication and SQL Authentication. Is there another setting or something that would prevent me from being able to log in to my remote server using 'sa'?
I'm so puzzled because I clearly can connect to the remote DB if I'm using a different user, but I can't connect using 'sa'.
Are there additional settings I have to configure on the remote server? I'm also a bit frustrated with this because I can't print out what the actual error code is. It just tells me that my login failed when I print the error message.
I figured out my problem.
For the new user that I created (see the original post's comments), it couldn't connect because of my connection string.
I tried first making my connection string the following, which still worked connecting me locally:
Initial Catalog=master;User Id=<user>;Password=<password>;
I was confused because this still worked, despite me not setting a Data Source.
So what I did was try using "Server", and it worked:
Server=<remoteIP>;Initial Catalog=master;User Id=<user>;Password=<password>;
So it turns out it was an invalid credential error because it was always trying to connect to my local DB. Thanks for everyone's input.