c#.net connection string works with integrated security but not when specifying username and password

Question

I am new to sqlserver, I have a connection string as follows:

< add name="test" connectionString="server=dbServer\SQLEXPRESS;database=test;integrated security=SSPI;" />

and the site connects and works fine.

I created a user in SSMS 17.4, gave it a name

< add name="test" connectionString="server=dbServer\SQLEXPRESS;database=test;User Id=dbUser; Password=letmein;" />

the site fails saying "login failed for user 'dbUser'"

The user has following :

server roles: public, dbcreator

user mapping: default schema: dbo
database role memberships: db_owner, public

any idea why it fails on use of username/password?

Thanks

Check the SQL Server logs and post what the errors are in there, please. — Thom A
– Thom A ♦, Commented Apr 24, 2018 at 11:56
ah, in the log, 'server is configured for integrated authentication only' — Andie
– Andie, Commented Apr 24, 2018 at 12:01
And there is you answer. :) Change the server to mixed authentication. — Thom A
– Thom A ♦, Commented Apr 24, 2018 at 12:02

Dan Guzman · Accepted Answer · 2018-04-24 12:05:44Z

Likely causes include:

1) SQL instance is not configured to allow SQL auth

2) the login and/or password is wrong

3) the login does not have access their default database

Check the SQL Server error log as that will have more detailed info on the cause of the login failure. Those are not returned to the client for security reasons. The error log can be viewed from SSMS (Management-->SQL Server Logs) or via a text editor. The current log file is named "errorlog" (no extension) and located under the installation folder Log subdirectory by default.

Ashutosh Kumar Singh · Accepted Answer · 2018-04-24 12:18:04Z

Most probably you would not have configured your SQL Server authentication mode. enter image description here

Collectives™ on Stack Overflow

c#.net connection string works with integrated security but not when specifying username and password

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related