注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

G G I C C I

 
 
 

日志

 
 

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

2012-05-28 18:33:10|  分类: 数据库 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

在微软的SQL Server数据库中创建了登录名(create login)但是却无法通过验证用登录名登录。出现错误代号18456,如下:

http://www.microsoft.com/products/ee/transform.aspx?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18456

 

"Login failed for user ''. (.Net SqlClient Data Provider)"

------------------------------

"Server Name: "

"Error Number: 18456"

"Severity: 14"

"State: 1"

"Line Number: 65536"

The following message might also be returned:

"Msg 18456, Level 14, State 1, Server , Line 1"

"Login failed for user ''."

 

解决办法是修改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

   1: USE master
   2: /* Mixed Authenication script SR 01-14-10, can update SQL authentication for instances.
   3: works with SQL 05/08 not tested with SQL 05 instances*/
   4: DECLARE @INSTANCEID VARCHAR(30) 
   5: DECLARE @STRVERSION VARCHAR(30)
   6: DECLARE @SQLVERSION VARCHAR(30)
   7: DECLARE @CMD VARCHAR(2000)
   8: SET @SQLVERSION = (SELECT CONVERT(VARCHAR(20),(SERVERPROPERTY('productversion'))))
   9: SET @INSTANCEID = ((SELECT CAST(SERVERPROPERTY('InstanceName') AS VARCHAR)))
  10: 
  11: IF (SELECT REPLACE(LEFT(@SQLVERSION,2),'.','')) = 10 
  12: BEGIN
  13: SET @STRVERSION = 'MSSQL10'
  14: END
  15: ELSE
  16: IF (SELECT REPLACE(LEFT(@SQLVERSION,2),'.','')) = 9
  17: BEGIN
  18: SET @STRVERSION = 'MSSQL'
  19: END
  20: 
  21: IF @INSTANCEID IS NULL AND @STRVERSION = 'MSSQL'
  22: BEGIN
  23: SET @INSTANCEID = 1
  24: END
  25: ELSE
  26: IF @INSTANCEID IS NULL AND @STRVERSION = 'MSSQL10'
  27: BEGIN
  28: SET @INSTANCEID = 'MSSQLSERVER'
  29: END
  30: 
  31: 
  32: SET @CMD =    'xp_regwrite ' + 'N' + '''HKEY_LOCAL_MACHINE''' + ',' + ' N' +
  33:             '''Software\Microsoft\Microsoft SQL Server\'+ @STRVERSION + '.' +
  34:             @INSTANCEID + '\MSSQLServer'''+','+' N'+'''LoginMode'''+',
  35:             '+'REG_DWORD'+','+ ' 2' /*2 is mixed auth.*/
  36: 
  37: EXEC(@CMD)
  38: PRINT @CMD
  39: PRINT 'A restart of SQL is required for authentication changes to take effect'
  评论这张
 
阅读(396)| 评论(0)
推荐

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017