Microsoft documenation on SQL server certificate accounts and roles for auditng password change purposes

  • 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

  • So they know nothing about this - logins, certificates, roles. I guess that's typical and indicates the value of their audits.
    Try this and the section Certificate Based SQL Server logins
    Principals (Database Engine)

    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

  • @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.

  • 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.

  • @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. 

  • 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. 😉

  • PR accepted and merged. You now have an "official" MS doc on that page.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply