0

SQL Server supports creating logins using SQL, Windows, certificates and asymmetric keys. Is it possible to remove the option of creating logins with Windows authentication (and just allow SQL authentication for example)?

2
  • 2
    Perhaps you could try login triggers, but I guess they can always be worked around by a sysadm. learn.microsoft.com/en-us/sql/relational-databases/triggers/…
    – Nick.Mc
    Dec 29, 2019 at 13:48
  • Thanks Nick.McDermaid, that is the closest solution to what i am looking for. One stat puts more than 80% of all hacks as having internal connection/help. So preventing/reducing access to the db is important. Dec 29, 2019 at 16:12

1 Answer 1

3

Natively you cannot prevent using Windows Authentication in SQL Server.

SQL Server supports two authentication modes, Windows authentication mode and mixed mode.

Windows authentication is the default, and is often referred to as integrated security because this SQL Server security model is tightly integrated with Windows. Specific Windows user and group accounts are trusted to log in to SQL Server. Windows users who have already been authenticated do not have to present additional credentials.

Mixed mode supports authentication both by Windows and by SQL Server. User name and password pairs are maintained within SQL Server.

Microsoft also recommends using windows authentication over SQL Server logins.

We recommend using Windows authentication wherever possible. Windows authentication uses a series of encrypted messages to authenticate users in SQL Server. When SQL Server logins are used, SQL Server login names and encrypted passwords are passed across the network, which makes them less secure.

As commented by Nick you can programmatically restrict the creation of logins from Windows user account, trusted domain account, or Windows groups.

Reference: Authentication in SQL Server

A similar question was also answered here by Paul White.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.