0

I tried new connection via:

$conn = new PDO("sqlsrv:server=SERVERNAME;database=DBNAME;MultipleActiveResultSets=false", "username", "password");

and it did not work, however when I remove the username and password it works fine. username is the same as my windows login, I tried multiple combinations as DOMAIN\username or username@domain.com etc, nothing works. However when username and password are not present, then it connects fine.

The error is:

SQLSTATE[28000]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Login failed for user 'username'.

so to my question, in order to debug the problem. Is it possible to check what username is used when automatic authentication is used after the login succeed?

EDIT: so once upon login without username and pass i tried:

Select name FROM DBNAME.sys.syslogins

and it gave me "DOMAIN\USERNAME"

I have tried that several times even before, but still same error :(

EDIT2: is it in db loginlist authorization? I have tried SQL Studio to log in via Windows authentication - works fine, however when i sqich to SQL server authentication and use the correct username and password, it gives me same error again, like the username is not existing anymore...

Jiri Zaloudek
  • 326
  • 3
  • 19
  • https://stackoverflow.com/questions/4268100/how-to-find-loginname-database-username-or-roles-of-sqlserver-domain-user-who – Jay Blanchard Mar 04 '20 at 12:13
  • When you remove the username and password it uses Windows authentication. When you specify a username or password it uses SQL Login authentication. Do you have an SQL Login created on the server with the username/password combination you're trying to use? Does that SQL Login have a default database defined? Does that SQL Login have a user defined with appropriate permissions in that database? – AlwaysLearning Mar 04 '20 at 12:22
  • can I have one account defined to use both authentications? Windows Authentication as well as SQL login authentication? - that is what I ultimately need – Jiri Zaloudek Mar 06 '20 at 15:01

1 Answers1

0

So at the end, I have found out that one account can not be set to log in via SQL authentication and windows authentication as well. So If I can login via windows authentication (no username and pass entered), then only way is to create new account with same username and set it as SQL Authentication upon Login. Please correct me if Im wrong (and mainly someone who can test otherwise - because i have found security setting to the account which says "SQL Server and Windows Authentication mode" which is active anyway but doesnt work, and according to our database admin, this setting is not what its for)

Jiri Zaloudek
  • 326
  • 3
  • 19