SQL Server Security – Windows Authentication Only in SQL Server 2016

authenticationSecuritysql serversql-server-2016windows

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?

Best Answer

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:

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.

Related Solutions

SQL Server 2016 – How to Start CTP3 Service on Windows 10

Interesting. Glad you got the SQL service to start. It should be okay to leave it as NT AUTHORITY\LOCALSERVICE for testing CTP3 on your workstation. In general for production servers on a domain it is best to provision and use domain service accounts for the SQL Server and Agent services - see https://www.simple-talk.com/sql/database-administration/provisioning-a-new-sql-server-instance-%E2%80%93-part-one/ .

SQL Server 2016 Query Store – Problems and Solutions

This was a known bug with Query Store on Standard and Express.

Automatic data cleanup fails on editions other than Enterprise and Developer. Consequently, space used by the Query Store will grow over time until configured limit is reached, if data is not purged manually. If not mitigated, this issue will also fill up disk space allocated for the error logs, as every attempt to execute cleanup will produce a dump file. Cleanup activation period depends on the workload frequency, but it is no longer than 15 min.

The work around is to turn off cleanup policies and manually clear query store data.

to clear all query store data

ALTER DATABASE <database name> SET QUERY_STORE CLEAR

to selectively clear data

sp_query_store_reset_exec_stats

sp_query_store_remove_plan

sp_query_store_remove_query

The issue was first fixed in Cumulative Update 1 for SQL Server 2016:

Query Store automatic data cleanup fails on editions other than Enterprise and Developer edition of SQL Server 2016 (KB 3178297)

Best Answer

Related Solutions

SQL Server 2016 – How to Start CTP3 Service on Windows 10

SQL Server 2016 Query Store – Problems and Solutions

Related Question