1

I'm busy installing MSSQL 2016, and strongly considering using Windows Auth only. That can interfere with no local installation, but it has occurred to me that if another machine or process wanted to access this service, it would have to be granted a Windows User credential, that I can then admit to the database. Is this a real problem, and if so, what are steps I can take to solve it?

2

Using Windows authentication only is typically considered the preferred configuration for SQL Server. Windows authentication uses a series of encrypted messages to authenticate users, whereas SQL Server logins and passwords are passed across the network, which makes them less secure.

Windows Authentication confers an advantage in that there is a single centralized location for controlling access.

If you have a user who no longer should have access to anything on your network (for instance, an employee leaves the job), you simply disable their account in Active Directory, and voila, the user no longer has access across the board, including any SQL Servers where they have a Windows Authentication account.

If you don't have Active Directory for authentication you would need to create an account on the machine where SQL Server is installed for each user who wants access to SQL Server.

See the following links for further details:

Any security related reasons to prefer Windows Authentication over Sql Server Authentication?

Choose an Authentication Mode

This question shows how you can still use Windows authentication for an application/user from an untrusted domain.

This MSDN topic on Authentication in SQL Server via .Net has some more interesting details.

  • 2
    +1 for a very sensible answer even before the installer got to that question! – ProfK Aug 17 '16 at 14:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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