sql login failed for user X...windows authentication only

Question

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!

Answer 1

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.