Can SQLEXPRESS Authenticate using both SQL And Windows Authentication?

Question

My question is will SQLEXPRESS work in both SQL and Windows authentication modes? I believe the answer is no, but I am not sure.

We have a commercial application that will only work with SQL authentication. We already have a SQLEXPRESS 2008 server, but it is set up for Windows authentication. If I create a user with SQL authentication and try to log in, the Application log says

Login failed for user 'cnorton'. 
Reason: An attempt to login using SQL authentication failed. 
Server is configured for Windows authentication only. 
[CLIENT: 10.100.100.30]

So, do I need to deploy a separate SQLEXPRESS server, or are there settings unknown to me right now, that would allow both authentication modes?

Thanks.

score 3 · Accepted Answer · answered Aug 28 '12 at 19:08

3

You don't specify what version of SQL Server Express you're running but as far as I know this has always been and still is possible. The following article details how to do it with SQL Server Express 2012.

http://msdn.microsoft.com/en-us/library/ms188670.aspx

answered Aug 28 '12 at 19:08

joeqwerty

108,377
6
80
171

@octopusgrabbus Joeqwerty is correct, the link he provided will work for 2008 as well. – JMeterX Aug 28 '12 at 19:17

score 2 · Answer 2 · edited Aug 17 '13 at 16:39

yes we can authenticate both ways. Please follow the following steps:

Download and install SQL Server Managment Studio (SSMS).
Use SSMS to connect to your server, use Windows Authentication
Right-click server - Properties - Security tab
Select "SQL Server and Windows authentication mode" click OK button
Right-click server - Select Restart
Go to node Security-Logins
Right-click on login "sa", select properties
Enter password (twice) on the general tab
Select Status tab
Select "Grant" under "Permission to connect to database engine"
Select "Enabled" under "Login"
Click OK

Can SQLEXPRESS Authenticate using both SQL And Windows Authentication?

2 Answers2