Question

如何更改SQL Server验证模式，18456错误，用户登录失败

Answer 1

解决办法是修改SQL Server认证模式， 具体做法可以通过DBMS或者直接代码。
DBMS操纵参见微软MSDN，网址：http://msdn.microsoft.com/en-us/library/ms188670(v=sql.90).aspx

代码如下（引用：http://www.sqlservercentral.com/Forums/Topic439615-359-1.aspx）

USE master
/* Mixed Authenication script SR 01-14-10, can update SQL authentication for instances.
works with SQL 05/08 not tested with SQL 05 instances*/
DECLARE @INSTANCEID VARCHAR(30)
DECLARE @STRVERSION VARCHAR(30)
DECLARE @SQLVERSION VARCHAR(30)
DECLARE @CMD VARCHAR(2000)
SET @SQLVERSION = (SELECT CONVERT(VARCHAR(20),(SERVERPROPERTY('productversion'))))
SET @INSTANCEID = ((SELECT CAST(SERVERPROPERTY('InstanceName') AS VARCHAR)))

IF (SELECT REPLACE(LEFT(@SQLVERSION,2),'.','')) = 10
BEGIN
SET @STRVERSION = 'MSSQL10'
END
ELSE
IF (SELECT REPLACE(LEFT(@SQLVERSION,2),'.','')) = 9
BEGIN
SET @STRVERSION = 'MSSQL'
END

IF @INSTANCEID IS NULL AND @STRVERSION = 'MSSQL'
BEGIN
SET @INSTANCEID = 1
END
ELSE
IF @INSTANCEID IS NULL AND @STRVERSION = 'MSSQL10'
BEGIN
SET @INSTANCEID = 'MSSQLSERVER'
END

SET @CMD = 'xp_regwrite ' + 'N' + '''HKEY_LOCAL_MACHINE''' + ',' + ' N' + '''Software\Microsoft\Microsoft SQL Server\'+ @STRVERSION +'.'+ @INSTANCEID + '\MSSQLServer'''+','+' N'+'''LoginMode'''+', '+'REG_DWORD'+','+ ' 2' --2 is mixed auth.

--EXEC(@CMD)
PRINT @CMD
--PRINT 'A restart of SQL is required for authentication changes to take effect'