On Excel-VBA, I am trying to connect to my SQL Server on Amazon AWS/RDS (free tier). All I am getting is:
Run-time error '-2147217843 (80040e4d)': Login failed for user 'testuser'.
My code:
Dim conn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim ConnectionString As String
Dim StrQuery As String
ConnectionString = "Provider=SQLOLEDB;Data Source=***sqlserver_instance***.amazonaws.com,1433;Initial Catalog=TESTDB;User ID=testuser;Password=testpassword;"
conn.Open ConnectionString
StrQuery = "SELECT * FROM TestTable WHERE ThisField = '" & Sheets("ThisSheet").Range("ThisValue").Value & "'"
rst.Open StrQuery, conn
Sheets("AnotherSheet").Range("A1").CopyFromRecordset rst
I can connect, manage the DB and perform queries using SQL Server Management Studio using the same username as above.
NOTE: Mixed mode authentication is enabled on the SQL instance on Amazon AWS/RDS. I've also use different combination/parameters for my connection string but still the same... Also, I have a copy of the database on my local machine running SQLExpress and the code works using Integrated Authentication=SSPI; Tried creating a 'testuser' on my local machine with similar permissions in Amazon AWS/RDS, and I'm getting a similar error (login failed..).
Could this be a permission problem in the SQL server for the SQL user/login? What permissions does 'testuser' needs?
SQL Log reports:
Error: 18456, Severity: 14, State: 5
BUT the username I'm using is in there and can be used to connect thru SQL Server Management Studio..