Authentication in SQL Server identifies the caller through supported identity choices. My GDPR series considered those choices alongside access controls.
Authentication in SQL Server: choose the mode
Windows-only mode permits Windows authentication. Mixed mode permits Windows and SQL authentication. It doesn’t turn Windows authentication off. Where supported, Windows groups help manage joiners, role changes and departures centrally.
Windows connections can use Kerberos or NTLM, depending on configuration. SQL authentication uses server-managed credentials and needs appropriate password handling. Windows authentication doesn’t automatically enable Microsoft Entra ID authentication. Entra has its own supported-platform setup.
Authorization determines what an identified caller may do. Map users or groups to the required roles and permissions. Separate administrative and application identities, avoid shared personal credentials, and review effective access over time.
Contained database users can reduce some instance-level dependencies. Their authentication and access still need deliberate design. Privacy requirements also involve purpose, minimization, retention, auditing and recovery. The main GDPR article covers that wider context.
Check the application identity and its access
For a practical review, start with the identity that the application actually presents. A successful connection proves that the identity was accepted. It does not prove that the account can read a required table, execute a procedure, or change data. Check those operations separately with the application’s intended permissions.
For example, a reporting account may connect successfully while lacking access to a particular report’s source. Giving that account administrator privileges would hide the access problem and broaden its authority. Identify the required operation and grant the appropriate permission through the chosen access design.
I also record who owns the account and how its access is reviewed when a person leaves or an application changes. That connects the identity choice to ongoing administration without treating a connection mode as a complete privacy program.
Related reading
Authentication is not authorization, it is the identity check before permissions determine allowed actions.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
