September 12, 2017 at 5:45 am
Hi
I am looking for official Microsoft documentation on sql server certificate mapped logins and sql server roles.
These certificate mapped logins beginning ending with ## from my research are for internal use only. Their passwords are not known, show up as NULL and they should according to Microsoft not be deleted. They are not interactive logins and so can be excluded from any password renewal. I have found lots of sources for this on different blogs and sql sites etc but the Auditors require official documentation of same from Microsoft.
I have only come across this really that is of any use but its too short ...
https://social.technet.microsoft.com/wiki/contents/articles/32387.sql-server-logins-back-to-basics.aspx
I am looking for a more in depth explanation.
The same applies for SQL roles. SQL roles are not SQL logins, they are roles you grant to users hence can be ignored for password rotational purposes. However once again finding official Microsoft documentation to prove this is difficult.
If anyone has found some good Microsoft documentation on this and can share I would be very grateful.
Thanks In Advance
September 12, 2017 at 7:52 am
It's noted they are for internal use only so you can tell the auditor you can't touch things that are internal system use only.
And then this link has the "Logins created from certificates or asymmetric keys are used only for code signing. They cannot be used to connect to SQL Server"
CREATE LOGIN (Transact-SQL)
In terms of roles, you could show them the documentation on create and alter roles - there is no option for password as it does not exist. Just like you don't have an option to change a password for a loaf of bread you buy. You could give them the documentation about roles in general but you may need to provide the four docs for create, alter on database and server roles. The general one for logins, users, roles, permissions:
Getting Started with Database Engine Permissions
Sue
September 12, 2017 at 8:43 am
@Sue_H - I like the reference to the loaf of bread....very funny! I got a good laugh out of that 😉
The other links and suggestions are very useful. There really isn't a lot on the Microsoft site to confirm exactly what I am saying but your suggestions are better than I have found thus far myself.
September 12, 2017 at 2:24 pm
Nothing good out there. I emailed MS and got this link: https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/principals-database-engine
Perhaps that's the best one as it notes you should not delete them. I asked if I can submit a PR to add specific text there. No response yet.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
September 13, 2017 at 7:41 am
@Steve Jones - thanks for your help. There really isn't anything good on the Microsoft site but hopefully what I have found and what has been suggested by yourself and sue is sufficient to keep the auditors quiet for the moment.
September 13, 2017 at 8:36 am
FYI, I submitted a PR anyway and it's being reviewed. Just added a sentence about passwords, so hope that URL will get updated with my change and you can keep the auditors quiet for this review period. 😉
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
September 13, 2017 at 3:32 pm
PR accepted and merged. You now have an "official" MS doc on that page.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply