Registered Servers do not allow SQL Server Authentication in SQL 2016?
-
I'm trying to register some servers in a Central Management Server on a new SQL 2016 box. It seems the only options I'm given for authentication in registering a server is for "Windows Authentication" and "Active Directory Integrated Authentcation". The option for "SQL Server Authentication" is never given. On previous versions it was just "Windows Authentication" and "SQL Server Authentication" (even on SQL 2014) so I'm thinking there is either a new option or perhaps a bug in SSMS?
For the record - I don't think it's the server but rather SSMS 2016 since even attempting to register a SQL 2008 R2 - 2014 server does not give the option. Also the option does not appear even if I try to register a server under a CMS that is an earlier version of SQL.
Any ideas? Anybody seen this before?
-
One last thing - if I use a SSMS earlier than 2016 and then connect to the 2016 CMS server and try and register a server I cannot even select an option for authentication - they are all grayed out. I suppose this is as designed though.
-
It's by design: https://msdn.microsoft.com/en-us/library/bb934126.aspx
3.In the New Server Registration dialog box, select the instance of SQL Server that you want to become the central management server from the drop-down list of servers. You must use Windows Authentication for the central management server.
-
In Registered Servers, "Local Server Groups", save Credentials with either SQL or Windows;the data is stored in your %appdata% folder, so it is user specific and not shared.
Central Management Servers, as Beatrix Mentioned, Are AD only
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Actually, it says
In the New Server Registration dialog box, select the instance of SQL Server that you want to become the central management server from the drop-down list of servers. You must use Windows Authentication for the central management server.
this is not true, I can use SQL Server Authentication to register a CMS with SSMS 2016.
On the "Create a Registered Server" Page it says you can use SQL Server Authentication which I cannot.
It's like these two are backwards.
-
ElementZero (11/21/2016)
I don't see where it says that you may use Windows Authentication only...? In fact on the "Create a Registered Server" Page it even says you can use SQL Server Authenticationit's subtle, but the page you are pointing to says the rules for both; it doesn't explicitly say" local server groups" can used both SQL and windows authentication, though.
later in the page, it's implied that the interface is the same, but you cannot use SQL users for CMS.
There are two kinds of registered servers:
Local server groups
Use local server groups to easily connect to servers that you frequently manage. Both local and non-local servers are registered into local server groups. Local server groups are unique to each user. For information about how to share registered server information, see Export Registered Server Information (SQL Server Management Studio) and Import Registered Server Information (SQL Server Management Studio).
System_CAPS_ICON_note.jpg Note
We recommend that you use Windows Authentication whenever possible.
Central Management Servers
Central Management Servers store server registrations in the Central Management Server instead of on the file system. Central Management Servers and subordinate registered servers can be registered only by using Windows Authentication. After a Central Management Server has been registered, its associated registered servers will be automatically displayed. For more information about Central Management Servers, see Administer Multiple Servers Using Central Management Servers. Versions of SQL Server that are earlier than SQL Server 2008 cannot be designated as a Central Management Server.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
sorry - seems I edited my response after you made your post 🙂
-
glad you've got it sorted out.
I have to use both in my environment;
we have some tunnels to another domain where the AD accounts are not trusted, so I've got to administer some SQL servers using SQL credentials;
those are in my local groups, and everything in my domain exists in a central management server collection;
I use them to deploy common tools to all SQL instances a LOT.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
ok makes sense.
Yeah I've used CMS a lot in the past - really beneficial. I've just never had to use one with SQL Server Auth before (special server that can't be on a domain).
Thanks for the help!
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply