Central Management Server

Question

I already have a SQL 2008 named instance configured as a Central Management Server.  I am able to successfuly execute queries against all registered servers without any problem.  I would like to use the CMS to change my sa password across all servers.  I was testing it on 3 servers and got an error of "Cannot alter the login 'sa', because it does not exist or you do not have permission." 
I have sysadmin permission and I am a local admin on the servers that has been granted through an Active Directory group that I'm a member of.  Shouldn't that work?

If I set my domain id up separately with sysadmin permissions (rather than getting my permissions through the AD group), I can change the sa password without a problem.

Doesn't the CMS work with Active Directory groups?  Has anyone else experienced this issue?

-- thank you
Toni

Answer 1

Can you try the query in CMS to make sure you have admin access in all the servers. If it returns 1 then you have admin access, else you don't.

SELECT IS_SRVROLEMEMBER('sysadmin')

In addition to this can you try using sp_password procedure to change the passwords, not sure whether this will help, but you can give a try.

---

Vidhya Sagar. Mark as Answer if it helps!

Answer 2

Go to Server property > Select Security

Make sure Server authentication is SQL Server and Windows Authentication mode.

HTH

Answer 3

Yes, I have sysadmin on each of the servers.  The query returns 1 for each server.
I tried sp_password again on the 3 servers in my test group.  One failed on me which is pretty similar to the results I was getting before.  This time, when I removed the 'Enforce Password Policy' option, it worked fine.  It is odd that it would fail on that, though.  The new password is accepted on all of ours servers.  I don't know what would be different with this one.

So, maybe the issue is just with the options the sa account has?  Although, the original error I was getting was clearly denying my id access to the server.

Toni

Answer 4

Thank you for your reply.  Yes, all servers that I'm trying to change the sa password on are set to SQL Server and Windows Authentication mode.

Toni

Answer 5

Can anyone assist?

---

Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com