Domainless Access to MS SQL Server Tutorial

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

• Have a JumpCloud account (you can sign up for a JumpCloud Free account if you don’t have one yet)
• Deploy agents on your server and workstations
  • Workstations do not need to be Professional editions of Windows
• Add devices to JumpCloud and bind users to device
• Ensure that NTLM isn’t disabled
• Create a User Group for SQL admins in JumpCloud
• Know your local user names
• [Optional] Have a VPN set up (ideally utilizing a RADIUS service) for remote users

Create a Local Group

Add User to the Local Group: 

(After the user was created in JumpCloud)

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 

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.

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 out. Get started with a free 30 Day Trial of JumpCloud.

Special thanks to Idan Mashaal at Plus500