Traditionally, there have been two options to authenticate into Microsoft鈥檚 SQL Server: Integrated Windows Authentication (managed by Active Directory) or SQL Server鈥檚 built-in authentication scheme. The first requires a domain controller, while the latter sends (encrypted) credentials over the wire when its 鈥淔orce Encryption鈥� flags are configured. In both cases, there is additional IT management overhead required to get users access to this particular server.
There鈥檚 now a third method: local group access through 探花大神 and Integrated Windows Authentication. It enables remote users to work with SQL Server without passing credentials around the web and without the need for users to run their access requests over a VPN and through the domain controller.
This article is a follow-up to a previous example that discusses domainless Windows File Sharing. The methods are similar, except in this example IT administrators in smaller organizations gain the additional advantage of enabling DBAs to work with SQL Server without having to stand up a domain controller. The DBA gains access to SQL Server with the same credentials they use to log into their devices and cloud services, with a single managed identity that integrates with fully-supported Windows authentication methods and SQL Server. This is all possible without the introduction of any software other than the 探花大神 agent.
This tutorial outlines the steps involved to create and manage local user groups for SQL Server through the 探花大神 console in addition to outlining how to harden Integrated Windows Authentication (NTLM) for better security. You can also monitor access logs for any suspicious events with Directory Insights. 探花大神 accounts are a prerequisite for this workflow.
Prerequisites
- Have a 探花大神 account (you can sign up for a 探花大神 Free account if you don鈥檛 have one yet)
- on your server and workstations
- Workstations do not need to be Professional editions of Windows
- Ensure that NTLM isn鈥檛 disabled
- Create a User Group for SQL admins in 探花大神
- Know your local user names
- [Optional] Have a VPN set up (ideally utilizing a RADIUS service) for remote users
New-LocalGroup -Name “SQLServerUsers”
(After the user was created in 探花大神)
Add-LocalGroupMember -Group “SQLServerUsers” -Member “yourname”
Select Users and Groups within SQL Server
Open Security > Logins in the SQL Server Management Studio GUI or <your server>\Databases\<your database>\Security\
- Click New User
- Navigate to the General page and search for your local user group
- Click Object Types and Select Groups within the dialog box, and click Ok.
Hardening NTLM
Get-ItemProperty -Path ‘HKLM:\SYSTEM\CurrentControlSet\Control\Lsa\’ -Name ‘LmCompatibilityLevel’
New-ItemProperty -Path ‘HKLM:\SYSTEM\CurrentControlSet\Control\Lsa\’ -Name ‘LmCompatibilityLevel’ -Value 5 -Force
You may also consider disabling WLAN for remote users only. Disabling this on PCs have use WLAN for their proxy configuration on a LAN could disrupt internet access.
HKLM\SYSTEM\CurrentControlSet\Services\WinHttpAutoProxySvc\Start
REG_DWORD4
Conditional Access
闯耻尘辫颁濒辞耻诲鈥檚 limits which devices may access applications and other resources through SSL/TLS certificates. It鈥檚 fundamental to , which can further secure access to resources via geofencing and other measures such as requiring MFA. These features are included in the 探花大神 platform without additional services required. 探花大神 also provides policies to ensure that Windows updates are applied and not delayed, which helps to ensure that any high-priority security patches are delivered to your Windows endpoints.
Monitoring also helps to ensure that your configuration isn鈥檛 being misused.
Directory Insights
探花大神 Directory Insights provides an audit trail of user logins so unauthorized attempts will be noticed and you鈥檒l be alerted. Directory Insights is a component of the platform and has no additional cost to access.
Try 探花大神
Don’t take our work for it: test it out. Get started with a聽free 30 Day Trial聽of 探花大神.
Special thanks to at Plus500
