SQL Server镜像后一个非常奇怪的报错,大虾救急！！

Question

2台服务器149.208.108.70 和 149.208.108.87 分别在域帐户cn\mssql01和cn\mssql02账户下安装SQL Server2005 Standard Edition sp3,然后对这台服务器上SQL Server数据库实施数据库镜像,

由于都在相同的域中,所以设置SQL Server的服务均由域管理员账户启动;

其中149.208.108.70 上的数据库为principal库,149.208.108.87 的数据库为mirror库

通过右键点击数据库->任务->mirror...->configure database Mirroring Security Wizard来配置数据库镜像,最终镜像配置成功,

但是运行一段时间后,Mirror库所在的数据库实例的SQL Server Logs 报错:

Login failed for user 'CN\MSSQL01$'. [CLIENT: 149.208.108.70]

Error: 18456, Severity: 14, State: 11.

请问这是什么情况啊?

我google了一下,貌似这个报错的意思是:

Valid login but server access failure...

 

这个应该怎么解决呢?

Answer 1

可能是设置问题,最好能详细描述一下步骤.

'CN\MSSQL01$' 这个账户什么?看起来像机器名?

不知道为什么会用这个用户区登录, 你可以检查一下mirror上是否有这个登录

select* from sys.server_principals

 

---

有dba的职位吗(北京的)，请联系我 stswordman#hotmail.com

Answer 2

This is valid machine account, ensure server has sql login for the account and it has proper permission in the db.

Answer 3

CN是我们的域名,主库和镜像库的服务器都在这个域中

MSSQL01是我们主库数据库服务器的hostname,其该服务器的Windows登录名是CN\mssql01

'CN\MSSQL01$'这个账号我不清楚是从哪里来的,因为域里面只有cn\mssql01这个账号,后面是没有'$'的

镜像服务器上查询

select* from sys.server_principals,也没有'cn\mssql01$'这个账号,

Answer 4

不太理解为什么这个登录名是合法的,因为我们的域中是没有'cn\mssql01$'这个账号的,

另外请问 ensure server has sql login for the account 是什么含义?是否是要在镜像数据库实例中创建这样一个账号?是windows认证方式还是sql和windows混合的认证方式的账号呢?

同在这个域里面的另外2台配置几乎完全一致的数据库服务器进行数据库镜像,就没有这个报错,同时也都是没有以相互服务器名为账号的登录账号的...

谢谢！

Answer 5

如果想通过server本身的账户去访问资源，就会转换成domian\computerName$，

这是个windows账户。

主要是不清楚为什么你的应用回去使用server本身访问，你可以暂时先创建一个账户，赋予相应权限，追逐相应的操作

---

有dba的职位吗(北京的)，请联系我 stswordman#hotmail.com

Answer 6

就是做了数据库镜像以后出现的这个问题,镜像也是成功的

我想是不是因为镜像主库在向镜像库同步数据的时候需要登录镜像库,而是用的账号就是这个'CN\MSSQL01$'

但是我不理解的是,几乎完全相同的配置,为什么另外2台镜像服务器没有这个问题呢？

 

Answer 7

可能还有其他的不同，你追踪一下cn\mssqloq$

---

有dba的职位吗(北京的)，请联系我 stswordman#hotmail.com

Answer 8

Did you create sql login for cn\mssql01 and cn\mssql02 in both servers with sysadmin rights?

Answer 9

没有,需要创建吗?在另外一台正常运行的镜像(即没有报错的镜像,我们共有2组镜像服务器)服务器上没有创建这样的登录名的啊

谢谢！

Answer 10

如何追踪cn\mssql01$ ?

使用SQL Profiler看不出来有什么异常,如果可能,能否麻烦请您帮我分析下我这边跟踪的tracelog

Your time is very much appreciated！

Answer 11

Add them to sysadmin in both servers, they need this rights to mirror db.

Answer 12

我已经在这两台服务器上的SQL Server实例中分别创建了以对方服务器登录名命名的sql login name(Windows认证方式),默认的Database是master,授权sysadmin...

目前还在观察

Answer 13

我已经在这两台服务器上的SQL Server实例中分别创建了以对方服务器登录名命名的sql login name(Windows认证方式),默认的Database是master,授权sysadmin...

目前还在观察

观察的结果是,还是SQL Server Log中还是存在这样的报错,

Date  11/22/2010 8:01:38 AM
Log  SQL Server (Current - 11/22/2010 8:01:00 AM)

Login failed for user 'CN\MSSQL01$'. [CLIENT: 149.208.108.70]

Error: 18456, Severity: 14, State: 11.

Answer 14

Again, did you create sql login for cn\mssql01$ on that server? How did you set security when config db mirroring?

Answer 15

Again, did you create sql login for cn\mssql01$ on that server? How did you set security when config db mirroring?

Actually,i can not understand you very well

'create sql login for cn\mssql01$ on that server' means create a Windows Authentication account named 'cn\mssql01$' on mirror SQL Server?

and i configue sql mirror with the Configure Database Mirroring Security Wizard,when it indicates me connect to mirror sql server instance ,i connect to mirror server with 'sa' account(SQL Server Authentiction),when i finish this wizard,it does not rise any errors..seems it's successful......

Your time is very much appreciated!

Answer 16

On which server did you get the error? Create sql login for the machine account on that server.

Answer 17

On which server did you get the error? Create sql login for the machine account on that server.

 I' m curious of the domain user 'cn\mssql01$',cause in our domian,we don't have this user(we have a domain user cn\mssql01 with no '$'),but i have to create this account for a sql login name with windows authentication.

 

 

Answer 18

This is not user, it's machine account as I said from beginning.

Answer 19

This is not user, it's machine account as I said from beginning.

I'm very grateful to you for your help,the error does not appears since i added 'cn\mssql01$' as a sql login name in our mirror server.I was wandering if this machine account is necessary when i configure database mirror with Database Mirroring Security Wizard,Or the error comes?

So if i configure our sql mirror by using certificate ,we may not need this machine account?

 

Answer 20

Usually use certificate on machines that are not in domain.

Answer 21

Thanks