How to Securely Access Data on SQL Server

John Marsland explains the methods of authentication in SQL Server databases, and shares some tips for keeping your data secure.

There are several methods for authentication to Microsoft SQL Server that can securely access the data that you need. Which method is best? Which is available on the platforms that you support? Let’s explore the options you might have.

Three Methods of SQL Server Authentication

There are essentially three methods used for authentication to SQL Server: SQL Server authentication, NTLM and Kerberos.

• With SQL Server authentication, the driver presents a User ID and password to the server. If the User ID and password are on the list of valid users that the server maintains, a connection is allowed.
• NTLM authentication (sometimes referred to as Windows Authentication) uses a more complex and encrypted negotiation with the server. As a result it is more secure than the simple User ID/Password approach.
• With Kerberos authentication, the client requests service and receives an encrypted “ticket” from a separate Kerberos authentication server (typically Active Directory in a Windows environment). The client then uses that ticket to gain access to the server.

One advantage with Kerberos (and NTLM in a Windows-only environment) over SQL Server authentication is that you have the opportunity for a single sign-on. When you need to sign on to additional servers or applications, the Kerberos ticket can be used to authenticate you to additional resources.

Kerberos is now the default choice for most Windows environments. However, in cases where there is no Kerberos server, or when the network environment doesn’t allow for Kerberos authentication, NTLM can be used to securely access data.

With DataDirect, You Can Choose Any SQL Server Authentication

Progress® DataDirect® provides the broadest range of authentication methods on the market. As the chart below shows, our driver supports SQL Server Authentication, NTLM and Kerberos with ODBC and JDBC, across a full array of platforms.

Progress DataDirect	Windows Client	Red Hat 5.0, 6.0 Linux Client	Unix (AIX, HP-UX, Solaris), SUSE, Red Hat v4.0 Client
ODBC	SQL Server Authentication · NTLM · Kerberos	SQL Server Authentication ·   NTLM · Kerberos	SQL Server Authentication ·   NTLM · Kerberos
JDBC	SQL Server Authentication ·   NTLM · Kerberos	SQL Server Authentication ·   NTLM · Kerberos	SQL Server Authentication · NTLM · Kerberos

Microsoft also provides several options for authentication in their ODBC and JDBC drivers, but no longer supports NTLM in the drivers. Support outside of a pure Windows environment is limited.

Microsoft	Windows Client	Red Hat 5.0, 6.0 Linux Clients	Unix (AIX, HP-UX, Solaris), SUSE, Red Hat v4.0 Client
ODBC	SQL Server Authentication ·   Kerberos	SQL Server Authentication Kerberos	Not supported
JDBC	SQL Server Authentication·   Kerberos	SQL Server Authentication ·    Kerberos	SQL Server Authentication ·   Kerberos *tested on Solaris only

DataDirect Enables Multi-Platform Authentication

Stovepipe data—data that is only available to one function in an enterprise—is increasingly becoming a liability. As we move into the world of Big Data, enterprises are finding that accessing data from anywhere in the company and often persisting that data in a Big Data store is very advantageous. Analytics across the entire enterprise is becoming the goal. Building an enterprise strategy where authenticated access to data is only possible from Windows machines or only some Unix platforms is often short-sighted.

For software vendors, a similar caution is in order. Having a Windows-only data access story is becoming a hard sell as the world evolves to more of an open-source mindset. A robust multi-platform authentication strategy significantly broadens the opportunity (and potential revenue) that a software vendor will see.

While Kerberos is a popular authentication method for many environments, there are configurations that do not need that level of security or cost. NTLM is sufficient in many environments, and an NTLM authentication capability across the broad set of operating systems is an appropriate direction for many software vendors.

Unlock a Full Range of Database Authentication Methods

DataDirect drivers provide a full range of database authentication methods. This is essential for both the modern enterprise moving toward Big Data and the open-source world, and for the ISV looking for broader opportunities.

What security features do you look for in your drivers? Let us know in the comments and check out our website for more information and a free trial.