Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

up vote 3 down vote accepted

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

share|improve this answer
    
Thanks. Added the version, 2008. –  octopusgrabbus Aug 28 '12 at 19:10
    
@octopusgrabbus Joeqwerty is correct, the link he provided will work for 2008 as well. –  JMeterX Aug 28 '12 at 19:17

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.