I am trying to connect to a local network SQL server via excel VBA but I get this run-time error:
[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'myUID'
When I use this connection string
ConnectionString ="Driver={SQL Server};Server=myLocalServer; Database=myDb;trusted_connection=yes;UID=myUID;PW=myPWD;"
I have Windows Authentication as the Authentication Method for the SQL server.
Do I need SQL and Windows Authentication? Is there something wrong with my connection string?
2条答案
按热度按时间8yoxcaq71#
If you set a UID and PW, then it's more than likely a SQL account. Does that account exist in SQL? When you remove UID AND PW, and replace with a trusted connection, it's then using windows authentication. If you do this, make sure your windows account has permissions in SQL.
jxct1oxe2#
The reason this happened is because you're asking it to do both a "trusted connection" and also passing it credentials (with uid and pwd). You need to remove
trusted_connection=yes;or removeUID=myUID;PW=myPWD;.