How to Securely Access Data on SQL Server

How to Securely Access Data on SQL Server

Posted on May 04, 2015 0 Comments

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

lock and keyboard

Is your data secure?

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.

John Marsland

John Marsland is Marketing Manager in the Data Connectivity and Integration business unit at Progress Software, covering the DataDirect and DataDirect Cloud products.

Comments

Comments are disabled in preview mode.
Topics

Sitefinity Training and Certification Now Available.

Let our experts teach you how to use Sitefinity's best-in-class features to deliver compelling digital experiences.

Learn More
Latest Stories
in Your Inbox

Subscribe to get all the news, info and tutorials you need to build better business apps and sites

Loading animation