Domainless Access to MS SQL Server Tutorial

Written by David Worthington on December 30, 2021

Share This Article

Traditionally, there have been two options to authenticate into Microsoft’s SQL Server: Integrated Windows Authentication (managed by Active Directory) or SQL Server’s built-in authentication scheme. The first requires a domain controller, while the latter sends (encrypted) credentials over the wire when its “Force Encryption” flags are configured. In both cases, there is additional IT management overhead required to get users access to this particular server. 

There’s now a third method: local group access through JumpCloud 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 JumpCloud agent.

This tutorial outlines the steps involved to create and manage local user groups for SQL Server through the JumpCloud 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. JumpCloud accounts are a prerequisite for this workflow.

Prerequisites

Create a Local Group

New-LocalGroup -Name "SQLServerUsers"

Add User to the Local Group: 

(After the user was created in JumpCloud)

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

Harden NTLM Using PowerShell 

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

JumpCloud’s Device Trust limits which devices may access applications and other resources through SSL/TLS certificates. It’s fundamental to conditional access, which can further secure access to resources via geofencing and other measures such as requiring MFA. These features are included in the JumpCloud platform without additional services required. JumpCloud 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’t being misused.

Directory Insights

JumpCloud Directory Insights provides an audit trail of user logins so unauthorized attempts will be noticed and you’ll be alerted. Directory Insights is a component of the platform and has no additional cost to access.

Try JumpCloud

Don’t take our work for it: test it our. Get started and sign up for JumpCloud, which offers free access for 10 users or 10 devices. You’ll receive complimentary premium anytime support during the initial 10 days of your account’s creation.

Special thanks to Idan Mashaal at Plus500

David Worthington

I'm the technical blogger for JumpCloud. JumpCloud certified, security analyst, a one-time tech journalist, and former IT director.

Continue Learning with our Newsletter