Authentication Mechanisms for Access + SQL-Server-Applications

by Philipp Stiefel, originally published February 26th, 2019, last updated February 27th, 2019

Photo by Omar González. Used here under CC0 licensing

Microsoft SQL-Server is a much more secure platform for your data than a Microsoft Access backend database can ever be. However, there are several options and strategies for user authentication and authorization in SQL-Server based applications. The overall security of your application significantly depends on the right fitting choice for your application. This text should give you some advice on what to consider before making that choice.

Before we get to some frequently asked questions, let’s define the scope of this text and get an overview over the different options of connecting the users of your Access application to SQL-Server.

Scope of this text

This text has been written with the scenario of a 2-tier desktop application in mind. That is an application that is running on the user’s computer and is connecting directly to the SQL-Server backend database.

Microsoft Access applications using OleDb- or ODBC-Connections to a SQL-Backend are perfect examples for this type of application. But most of this text is equally valid if your client application is written in VB.net, C#, Delphi, or any other language. The basic ideas and concepts, even though not the terminology and technical details, are also valid for other backend databases like Oracle, MySQL, PostgreSQL, and any other serious DBMS around today.

Many of the basic concepts and mechanisms are valid for 3+-tier applications too. However, the assessment of the pros/cons and the security issues of the individual approaches will be significantly different in such scenarios. Examples for 3+-tier applications are web applications where the user’s browser will connect to the web server, or an enterprise desktop application connecting to an application server running a middleware layer, instead of connecting directly to the database server.

Shared “Technical User Accounts”

Actually, I’m not sure if the term Technical User is used internationally. In the German-speaking area the term refers to a user account that is not owned by a person but by a process or application.

As this type of user account is not bound to a real person but to an application that is used by several different users, the credentials to that account are stored inside the application. Regardless who is actually working with that application, he/she will be authenticated to SQL-Server with the user account embedded in the application.

I think this authentication mechanism is fundamentally flawed and unsuitable for most types of applications. But, before we get to the numerous drawbacks of this approach, let’s look at its few merits.

Pros

Little administrative effort

There is only very little administration effort required on SQL-Server. You would just create a single Login on SQL-Server and assign that login permissions to access the application’s database with the maximum set of permissions required by the highest privileged user of your application. (That is a huge con as well!)

Access control implemented with familiar technology

More fine-grained authorization and access control is implemented inside your application using VBA and Access concepts you are familiar with. So, there is no learning curve involved if you are already experienced with this in a plain Microsoft Access context. – But, to be honest, I’m not entirely convinced that this a pro rather than a con, either.

No access to data without the application

Users will only be able to access the SQL-Server database by means of your application. The merit of this pro is again a very subjective one and mainly based on the assumption that the access control mechanisms in the backend are not very sophisticated.

Cons

Password stored in the application file

The credentials to access the SQL-Server database need to be stored inside your Access frontend database file. This is a security vulnerability! I’ve seen many very creative approaches to hide the user name and particularly the password inside the file. Several of which were so sophisticated that I would consider them to be reasonably secure. Still, the basic fact remains, the password is stored inside the file and can possibly be discovered by a determined attacker with access to the frontend file.  

Hard to change the password or additional password required

All the approaches I would consider to be reasonably secure, involve …

… either the password to be hidden in the compiled code of the application and the application being only available as a compiled mde/accde file. This implies that changing the password requires modifying the application source code, recompiling the application and distributing a new version of it to the users.

…or, if the password is stored anywhere else in the file, the frontend file being encrypted and secured by a password on its own. In my opinion, the user having to enter his password is an avoidable nuisance. Furthermore, the backend password would then not be protected against any attacker who knows the frontend password to decrypt the local file. The frontend password must be shared by all the users of the application. – Something I would rather discourage.

Permission administration needs to be implemented

If you want to use different levels of permissions in your application, you need to built custom data structures and the UI to manage that permissions data in your application. This is not a big deal for simple permissions systems, but it will become increasingly harder with growing complexity of your permission system.

Authorizing/Identifying the user (becomes harder)

SQL-Server has a set of built-in functions to easily identify the user of a session/connection on the server side. You would need to …

… either replicate all these functions to work with your hand-rolled permission system. - That is much more complicated than it appears at first glance!

… or implement all user authorization/identification in the client application. This is doable, but certainly more error prone than build on SQL-Server’s proven user authentication/authorization system. Furthermore, it is likely that changes to the permission concept require code changes, recompilation, and new version deployment.

Application user has high permission set on the server

As mentioned among the pros above, the application’s technical user has to have the permission level of the highest privileged user of your application. So, malfunctioning in your own access control or password-protection code will automatically result in the high privilege login being exposed to accidental or intentional misuse.

Individual SQL-Server-Authentication Logins

SQL-Server-Authentication means you create individual logins on the SQL-Server-Instance your database is running on. There needs to be an individual account for each user that should have Access to your application. Users authenticate themselves by entering their user name and password when connecting to SQL-Server.

Pros

Full SQL-Server permission system

Each connection will be clearly associated to the individual user establishing the connection. You can use the full SQL-Server Permissions System to define fine grained access rights to your database objects down to the column level. Permissions will be automatically checked by the server whenever any user accesses any data in your database.

The concept of Database Roles of SQL-Server can be used to streamline administration without you having to implement the data structures for different graduated access levels. If you are content with using SQL-Server’s administration tools for managing user permissions in your database, you do not even have to implement any UI for user management in your application at all.

You can use SQL-Server’s build-in TSQL security functions to retrieve the user identity. If you want to, you can implement additional custom user access control fairly easily and securely on the server side.

Does not require Windows Active Directory

SQL-Server-Authentication does not require the client computer/user be part of a Windows Domain. It works independently of Windows Active Directory and thus allows users to connect from non-Windows devices and from outside the SQL-Server’s domain (if any).

This is particularly relevant if your database runs in SQL Azure and you cannot use Azure AD for SQL-Authentication for whatever reason or if the organization owning an on-prem server has no Active-Directory.

Cons

Initial steep learning curve

If you never worked with SQL-Server before, it will certainly require a certain amount of initial practice before you are sufficiently familiar with SQL-Server’s security and permission system to implement reliable access control to a complex application. – But let’s be honest here. If you put your data into a SQL-Server database, you will need to get accustomed to security concepts of SQL-Server anyway.

Cooperation with the SQL-Server administrator

As each user Login has to be created on the SQL-Server-Instance, it must be created by a person having administrator access to that instance. If there is a large number of users of your application and some fluctuation in the user base, this will add to the workload of the administrators.

Potentially unencrypted password

If client and sever support it, the password is encrypted when a client establishes a connection to SQL-Server. However, if the client does not support it and the server is not configured to require encrypted connections, the password might be transmitted in clear text over the network. – This will not be the case with your Microsoft Access application using an ODBC-Connection, but it may be the case if any third-party-tools are connecting to the server.

Authentication via Windows Active Directory

The recommend and most secure authentication mode available for SQL-Server is Windows Authentication Mode. With this authentication method, the password will never be a sent to the SQL-Server-Instance. Instead the identity of the connecting user will be verified by the Windows operating system and only confirmed to SQL-Server with the Windows principal token of the user.

Beyond the basic authentication process, Window Authentication Logins are similar to SQL-Server-Authentication Logins.

Pros

Full SQL-Server permission system

As with SQL-Server-Authentication, each user will be identified individually, and you can use all features of the SQL-Server Permission System as described for SQL-Server-Authentication Logins above.

Based on core Windows feature

Windows Authentication in SQL-Server is based on the Windows-Active-Directory logon mechanism which uses the Kerberos security protocol. This protocol is secure and widely used. Even if there should be a security issue with the protocol or with Microsoft’s implementation of it, you can be sure that Microsoft will address such issues with extremely high priority, as this is vital to core Windows user authentication.

User administration can be delegated to system/network admins

Other than with SQL-Server-Authentication you do not need to maintain individual SQL-Server-Logins for each and every user of your application. Instead of individual user Logins, you can create Logins for Active-Directory-Groups and tie the permissions in your database to these groups. By this approach you can keep SQL-Server-Login administration to a minimum. After all the required Active-Directory-Groups are initially added, you would only need to change any SQL-Server-Login if there were changes to the structure inside the Active-Directory. So, user administration is automatically delegated to the Active-Directory administrator, who needs to maintain the users anyway, and it does not put a burden on the SQL-Server administrators.

If your Active-Directory is sensibly structured and maintained, Active-Directory-Groups will be sufficient to assign the desired permissions to different sets of users in your database.

Even if you use Active-Directory-Groups instead of individual users, every individual user will still be identifiable with his Windows user name by the TSQL security functions to retrieve information about the current user.

Cons

Active-Directory required

There is an obvious disadvantage of Windows-Authentication in SQL-Server. It requires a Windows-Domain-Controller with an Active-Directory and the SQL-Server has to be integrated into that domain. This might be a limiting factor if you use an Azure SQL Database but not Azure AD.

If there are only some users that cannot use Windows-Authentication, you still can use it for all the other users and create SQL-Server-Authentication-Logins for the users unable to use Windows-Authentication.

(For pure testing/development purposes Windows-Authentication also works if there is a user account with the same name and password on the server as well as on the client. – This is an acceptable approach only in the tiniest of production environments!)

Initial steep learning curve

As this type of login uses the SQL-Server permissions system as well, you’ll have to learn how to use it to make your application secure. 

How to prevent users with Logins from connecting directly to the server?

Now that we have a solid overview over the different options of authentication, we are ready to get to a common question and the actual trigger for me writing this text.

You might wonder whether you missed me discussing the problem that individual User Logins on SQL-Server, regardless whether they use SQL-Server-Authentication or Windows-Authentication, will allow any user to connect to the server using any application of his choice. I was asked this question multiple times and I often heard or read the opinion that individual user logins are less secure and unsuitable for databases containing sensitive data for that reason.

The solution proposed by several experts in the Access community is to use a technical user account for an application dealing with sensitive data. The individual users do not get permission to connect to the server in their own right and can only use the application to connect and work with the data.

I fundamentally disagree with this concern and even more so with the proposed solution! The drawbacks of a shared user account are discussed above in detail already. But there is more to it in the context of this question.

Let’s look at some concerns in more detail:

A user could connect using plain Access, Excel, or any other tool and see confidential data that would be hidden from him by a custom frontend application.

You should not grant users access to tables containing data they are not supposed to see. Instead create a view, function, or stored procedure that returns only those columns and rows from the table the user is supposed to see. If you use distinct Logins, this is easily implemented. The identity of the user is available on the server side and can be used in any query to filter the data.

A user could connect using plain Access, Excel, or any other tool and download/export data while the custom frontend application would not allow that export.

This is true and it cannot be prevented. – But think about the problem. The application probably shows the very same data to the user in an Access form. Otherwise you would not need to allow this query on the server.

Now, please try this: Open that form in your application, press [CTRL]+[A] to select all records, press [CTRL]+[C] to copy the selection to the clipboard, and then paste the contents of the clipboard anywhere you like. - So, the issue exists, but your application does not prevent it either.

Ok, fair enough, maybe you could prevent copying records from your application. – But think about the threat some more. You are worried about a user being tech-savvy enough to set up a connection to SQL-Server and query data directly. - Don’t you think the very same user would also be able to take a screenshot of the data from your application’s forms and feed that screenshot to an OCR application? And its not too difficult to set up a keyboard and mouse automation tool to scale that process up.

A user could connect using plain Access or another tool and edit data directly in the tables, or views, or could run stored procedures.

Yes, he can. If your database contains adequate validation rules defined in constraints and triggers and the permissions for the stored procedures are set adequately, the user will only be able to do the exact same things he could do in your application. And of course, with proper permissions in place, he would not be able to edit any record he is not supposed to edit at all.

Bottom line assessment

If you implement proper server-side security, none of these potential threats can be addressed any better by using a client-side application to restrict access to the data. To the contrary, if you omit server-side security and just use client-side security, the impact of a successful attack on your data can be magnitudes more severe than otherwise.

I simply cannot imagine any scenario where you will achieve better overall security by favoring access control in the client-application over SQL-Server security.

Isn’t it a huge additional development effort to use the SQL-Server permission system?

That depends on your requirements. If your application works with highly sensitive data, I would not consider this effort “additional” but fundamental and mandatory. The simple approach with securing the data only inside the frontend application is just not secure enough in this case.

In general, I cannot deny that implementing a security concept in SQL-Server requires effort. If your data isn’t that sensitive and you mainly want to protect users form accidentally modifying data in ways that were not intended to, you probably can get away with a simple access control system implemented in your application.

In any case, keep in mind that once you created all the constraints, triggers, views, procedures and permissions on the server to wrap the data and keep it secure, this will always be in place and its use will be automatically enforced. Regardless whether you create just one form accessing the data in your frontend application or a dozen. The data will be secured according to the rules implemented on the server in all of them. You do not need to worry about having forgotten important security checks in one of those forms. – This puts the required effort into a different perspective.

Why not use SQL-Server Application Roles?

Application Roles are roles inside a SQL-Server database that can be assigned to a connection at run time rather than permanently to a User/Login. They were built to address the exact worries about exposing to much data to users with individual logins. An application can apply a custom Application Role to an existing connection that was established by its user. Thus, elevating the connections permissions to a higher level than the user would have had on its own.

To achieve that, application roles are secured with a password. The application would then, upon connecting to the server, use that password in a call to the system procedure sp_setapprole to assign the Application Role to the connection. Once again, that password needs to be stored inside the application, so, all the problems with securing the password for a technical shared user do apply to Application Role passwords as well.

But there is a much more severe technical issue preventing the use of Application Roles with most Microsoft Access applications. As describe above, you would need to execute a custom SQL Command to activate the Application Role on each connection your application uses. Unfortunately, you cannot do that with Microsoft Access. Microsoft Access has the habit of creating connections without you knowing it. If you have a form bound to a view or table from the SQL database and show data from additional tables/views in list boxes, combo boxes, or sub forms, Access will most likely open additional connections automatically to query the data for these additional elements. You have no possibility whatsoever to apply the application role to those additional connections.

So, while Application Roles are built to address exactly the concerns voiced here, they cannot be used due to the internal implementation of connection handling in Microsoft Access.

How to simplify user and permission administration?

Managing SQL-Server Logins, users, roles, and permissions can be a tedious and time-consuming task if not prepared and executed properly.

It will be sufficient to ignore the lowest level of configuration options for almost all applications. Meaning, you should mostly not deal with individual users but with Database Roles and Active-Directory-Groups instead. Define the roles you need and then assign permissions to those roles. Ideally you would then map the Active-Directory-Groups to your Database Roles. If you have to use individual Logins for some users, you would also assign these logins to roles in your database.

The same applies to the access permissions. Technically, you can configure permissions down to the column level. I strongly advise you not to do so. Focus on higher level objects like tables, views, procedures and functions instead. If you need to make distinct columns of a table accessible to some users but denying access to others, you can create two different views on the table one for each group of users. Then you manage permissions on the view level instead of column level. The effect is the same, but it is much easier to keep track of and to maintain.

Further thoughts on SQL-Server data security

There are other aspects of data security you should keep in mind as well. They are not in the scope of this article, so I’ll just mention some of them briefly.

Encryption of data

Regardless of how you authenticate your users, you also should protect the data while it is being transmitted between client and server. While your data is sent over the network if can potentially be read by someone who has access to the network infrastructure. This should be of particular concern if you connect to SQL-Servers on the internet, like when your server is hosted at Azure SQL. To prevent anyone from eavesdropping you should enable and enforce encrypted connections.

If you deal with very sensitive data, you should also consider encrypting sensitive data in your tables, your SQL-Server data files and/or your database backups.

Backup your data

While we are at it. Backups are mandatory for every professional database. Even with the most sophisticated security concept implemented, a privileged user might just accidentally delete data, or your hardware might fail and destroy data files in the process. SQL-Server has powerful backup features that can be scheduled with its Agent-Process to be run automatically.

You can even implement automatic backups with SQL-Server-Express-Edition, which does not include SQL-Server-Agent.

Conclusion

Microsoft SQL-Server has powerful features to protect your data right inside the database. You should learn how to apply the SQL-Server permissions system to maximize its benefits for your applications. This will take some time, but the reward will be significant. It enables you build secure applications that will be able to withstand malicious attacks from knowledgeable and determined attackers.

Acknowledgement

I had in mind to write this text for years. However, it still would not exist today if it wasn’t for Luke Chung of FMS. I’m just having an interesting discussion with him on SQL-Server-Security for Access-Applications and his excellent text on SQL-Server Database Users in Contained Databases. - After reading my musings here, you should not be surprised that I disagree with his concerns regarding Windows-Active-Directory-Users mentioned in that text. - This whole article basically came into existence to explain my differing view.

Share this article: