15

hey everyone, having trouble with some sql and c# interaction. I'm creating the DB and logins from scripts in a setup program, then the actual client program is supposed to login, but everytime i run the client program, it bombs and i get this error in the log file. (btw: the client program works on a preexisting DB on another machine.)

sql login failed for user 'user'. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: ] Error 18456, Severity:14 State:58

these are the commands used to create the login/user...

CREATE LOGIN [user] WITH PASSWORD='pass', DEFAULT_DATABASE=[data], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
EXEC sys.sp_addsrvrolemember @loginame = N'user', @rolename = N'sysadmin'
EXEC sys.sp_addsrvrolemember @loginame = N'user', @rolename = N'serveradmin'
USE [data] CREATE USER [user] FOR LOGIN [user] WITH DEFAULT_SCHEMA=[dbo]

what am i missing?
thanks for the help!

dave k
  • 1,329
  • 4
  • 22
  • 44

1 Answers1

31

Your SQL Server instance is setup to support Windows authentication only. Your create user script is attempting to create a SQL user account, which fails because the database instance only supports Windows authentication.

To allow your database to support your create user statement, open SSMS. Right click your server and click Properties. Go to the security tab, and change your Server authentication from Windows Authentication mode to SQL Server and Windows Authentication mode.

Once that's done, your script should likely work fine.

Shan Plourde
  • 8,528
  • 2
  • 29
  • 42
  • actually, it wouldn't fail when creating it, it would fail trying to login using it. The change in the server properties worked, but what would be the script to do this. i would rather not install SSMS onto all of our clients machines just to do this one task. – dave k Mar 07 '11 at 19:01
  • The security change from Windows to Mixed Mode is a one time change. As it's done one time, you needn't worry about installing SSMS on all of your client machines. To my knowledge, it's something that you have to specify when you install SQL Server, or something that you can override within SSMS, see http://stackoverflow.com/questions/1393654/how-to-change-from-sql-server-windows-mode-to-mixed-modesql-server-2008 for details – Shan Plourde Mar 07 '11 at 19:15
  • the program i'm working on uses microsoft's integrated publishing services that does a one click install of my prerequisites..one of which is sql server 2008 express edition. it only ask me to agree to the EULA when it installs the DB server. so are my options really limited to installing SSMS or manually installing MSSQL '08 express to change it to sql server and windows authentication mode? ...or i just found a way to do it in the registry... really would like to write a query to do so... – dave k Mar 07 '11 at 19:45
  • Is there any control that you may have with Microsoft Integrated Publishing Services? If it does the install of SQL Server via a command line script, then if you can change the command line script, there is a parameter to the SQL Server install called /SECURITYMODE that allows you to specify mixed-mode for the database, see http://msdn.microsoft.com/en-us/library/ms144259.aspx. I suspect that SQL Server Express supports the same options. – Shan Plourde Mar 07 '11 at 19:53
  • no, haven't found any controls editing the install in a command line or anything. sad day. Thanks for all the help though! – dave k Mar 07 '11 at 20:42
  • 1
    **NOTE:** I was racking my brain trying to figure out why after I changed the security settings to allow `SQL Server and Windows Authentication` it still failed to login and reported to the event log SQL Server was not set up to allow SQL Authentication. Restarting SSMS was not enough for me. I had to _restart_ the SQL Server Instance Service (also restarts SQL Agent). Then it worked perfectly. – atconway Jul 08 '15 at 03:24