How to leverage SQL Server Application Roles in MsAccess?

Mats 1

Does anyone know if there is a way to use SQL Server Application Roles from within Ms Access?

I have an Ms Access application with tables linked to SQL Server. Users of the application can access and manipulate data in the linked tables according to their role in the application. I have also granted users access to the tables on SQL Server, since users authenticate via integrated windows authentication. This however means that the users can also connect to the tables on the server directly, bypassing the protection implemented in the Access application, which is of course not ideal. The Application Roles feature in SQL Server seems to offer a nice solution for this problem, however, there seems to be no direct support for this feature in Ms Access.

An option I was considering is to create a proxy ODBC driver which activates the application role using credentials passed to it in the connection string. However, I have no previuos experience with developing such a solution, so am interested to here what other ideas might be out there!

Greg Youdale 5 Reputation points

2023-11-09T10:20:33.1333333+00:00

I am doing this, however, I did run into some issues two days ago that I am looking at now. My Access application is quite significant ...
We recently did a deployment, but quickly met some issues, that for some reason, did not come up in testing ...
In the application, the main tables are managed exclusively by executing stored procedures through a database connection that first calls sp_setapprole, and this seems to work fine.
Most comboboxes in the app are driven by stored procs, called in pass-through queries. These pass-through queries have their own connection strings, which are different from the connection that called the Application Role ... so maybe here is the issue ...
I'm still trying to track down what is actually happening ... and the question at present is 'When access runs one of the pass-through queries, is it respecting the permissions granted in the Application Role, or, because the query is using a different connection, is it simply using the permissions granted to the Windows login of that user ?' ... Ha, I think that I have just answered my own question ...
I've yet to get with the DBA to determine what is actually happening ... the same question also pertains to linked tables, that also have their own connection strings ...
I'm thinking that the connection that called the App Role is respecting the permissions of the App Role, but the other connections (pass-through queries and linked tables) don't know anything about the App Role and hence are just using the permissions granted to the database user ... might still have to grant permissions on the linked tables and stored proc in the pass-through queries to my database user ... might have to get rid of the linked tables completely, such that the database user is only granted EXECUTE on all the combobox stored procs ...
Erland Sommarskog 101K Reputation points MVP

2023-11-09T19:31:55.5666667+00:00

THe application role only applies to the connection where you call sp_setapprole, so if the passthrough queries and linked tables result in new connections, the approle will not apply on them.
Greg Youdale 5 Reputation points

2023-11-10T06:05:31.16+00:00

Yes exactly correct ...
I did give the manual SQL connection an 'Application Name' in the connection string and that name did correctly show up in SQL Profiler any time the app used that connection to EXECUTE a stored procedure.
Indeed, a separate connection (named Microsoft Office), also showed up in SQL Profiler when the app was updating the connection strings for the linked tables (and I suspect would also show up when the time came for the app to run a pass-through query, although that wasn't tested).
It is quite clear now that the AppRole pertains to the manual connection that was established in the Access app, however, does not pertain to any of the connections for pass-through queries and linked tables ...
If I could specify my manual connection as the connection object for the pass-through queries and linked tables, rather than just a connection string, I would be off sailing free using the Application Role ... but alas, I can only specify a connection string, which then uses a separate connection established within the depths of Access.
One saving grace is that much of the application's work is done using stored procedures via the manual connection (with AppRole permissions) and hence permissions to those tables can be removed from the database user. The permissions required by the database user are then only EXECUTE on the stored procedures that are called in pass-through queries and for the few linked tables (that I could get rid of) ... rather than granting permission on everything the app needs to the database user (who can then go to Excel or similar and start linking to tables ... so all is definitely not lost by using an Application Role.
Erland Sommarskog 101K Reputation points MVP

2023-11-10T07:52:11.3133333+00:00

Just to clarify: the application name you put into the connection string has no relation to your application role.
Greg Youdale 5 Reputation points

2023-11-13T09:10:01.56+00:00

Yes that is correct ... the 'Application Name' that i specified in the connection string was only added so I could see in Profiler, what connection was being used for what ...

3 answers

Erland Sommarskog 101K Reputation points MVP

2022-05-17T21:32:10.49+00:00

I don't know Access, so I can't say where in Access you would put in the application roles.

However, I like to point out that you can only achieve security-by-obscurity this way. The password for the application role must be available from the Access application, which means that users how know their ways can find it, and then employ it from SSMS.

If you want to achieve a fully secure solution, where users only can access the database through the application, you need to have three tiers, so that the middle tier connects to the database in a way that is not available to the user. (This can be achieved in a number of ways, both with networking and application users.)

Now, Access and three-tier application does not really go together, although you could put the Access application as a Remote Desktop application.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment
AlphonseG 191 Reputation points

2023-11-09T14:14:29.33+00:00

An alternative thought.

Use SQL Server Authentication. When you create logins in SQL Server, append an obscure set of chars ($%@!x) to the password you supply to the user.

So, you tell the user their password is marYJane1, when it is actually marYJane1$%@!x.

Obviously, you will need a login screen in your app. When the user logs in, you append the extra chars to their entered password and make the connection.

If they try to connect by any method outside of your app, the password they enter won't work.

Assuming you are distributing an ACCDE, it would be very difficult for anyone to figure out the extra chars.
Please sign in to rate this answer.
Greg Youdale 5 Reputation points

2023-11-10T06:08:03.99+00:00

The client only uses Windows authentication ... but this is a clever idea :)
Sign in to comment
Albert Kallal 4,651 Reputation points

2023-11-10T18:31:07.0233333+00:00

A few things:

You cannot use multiple connections to the same database for security.

Now, read the above again nice and slow!!!

So, if you using windows authentication for logons, then you don't really have different connections, and it does not matter, does it? (either you can use the SQL server, or you can't). When using windows auth, then member ship in roles etc. is managed by windows, and not by sql server.

If you using SQL server logons, then you can't use DIFFERENT logons with different connections from the ONE access application. I stated this in the first sentence here, and I mean what I say, and I said what I meant.

The reason for above is that Access will cache ANY valid connection to the database. That means if you opened a form, used code, used a pass-though query, it DOES NOT matter, but ONLY that some place, some how, some where you obtained a valid connection to sql server. The VERY instant you do this, then that user/password + valid connection is now cached by access.

And once the above occurs, if you THEN say use a table that linked using a read only connection's and logon? Well, since at SOME point in time you connected with a valid read/write logon, then that will be used!!!

Worse, is you really cant control WHICH cached logon will be used!!

What the above means is that multiple connections with different rights MUST BE REMOVED from your design assumptions here.

To be fair, this caching of valid connections is one of the best features of ODBC connections in Access, since this means you can link all of your tables WITHOUT including the user id + password. This means no user id and password exists in the linked tables, and thus someone opening up the application (or even attempting to import the linked tables to another application) will find that those links do NOT work (until such time you execute a valid SQL logon from Access).

While this is a "huge" increase in security when using linked tables, the downside of this knowledge is that you thus can't have multiple connection to the same database with different rights, and you can't do this, since you have NO control over which connection will be used by Access.

As for using roles from SQL server? I suppose if this is a compiled Access application (accDE),then a function to test/check for membership in a sql server role could be adopted, and thus forms could be prevented from being open by users without membership in such roles.

As for how the cached user + password system works, and how you can use this greatly to your advantage is explained here:

https://www.microsoft.com/en-us/microsoft-365/blog/2011/04/08/power-tip-improve-the-security-of-database-connections/#:~:text=Power%20Tip%3A%20Improve%20the%20security%20of%20database%20connections,one%20more%20hole%20...%207%20Extra%20reading%20
Please sign in to rate this answer.
Greg Youdale 5 Reputation points

2023-11-13T13:21:44.23+00:00

It is a very interesting conversation and it is the first time that I have found a Google hit on SQL Application Roles called from Access.

I have read the contents of the blog above regarding the reuse of an existing connection, rather than creating a new connection, if a new connection shares the same driver, server name and database name as the existing connection. That does make sense as I did wonder if there were multiple separate connections for multiple linked tables/pass-through queries.

In my code, as shown below, I first establish a SQLOLEDB connection and then subsequently use an ODBC connection for the linked tables and pass-through queries.

In this scenario, I can see that my first SQLOLEDB connection will not be reused as subsequent connects use a different driver (i.e. ODBC).

So this now begs the question :

If I first establish an ODBC connection, as shown in the blog (rather than the existing SQLOLEDB connection), and then call the Application Role through that connection, when the code then updates the connection string to the linked tables and pass-through queries, which will now be the same driver, server name and database name as the first connection, will the first connection be reused for those updates to the connection of the linked tables and pass-through queries ? ... and will the permission granted in the Application Role through that first connection now be in effect for all the subsequently connected objects ?

I will do some testing on this, but I am very interested to hear what others have to say ...

Below is a condensed version of my code and it can be seen in the code that I am using Windows authentication.

'1. create the connection string Dim sConnectionString As String sConnectionString = "Provider=SQLOLEDB.1;Data Source=MyServerName;Initial Catalog=MyDatabaseName;Integrated Security=SSPI" '2. Create and open a new adodb Connection object Dim MyConnection As adodb.Connection Set MyConnection = New adodb.Connection MyConnection.ConnectionString = sConnectionString MyConnection.Open '3. call the Application Role through the open connection Dim cmd As New adodb.Command Set cmd.ActiveConnection = MyConnection cmd.CommandType = adCmdText cmd.CommandText = "EXEC sys.sp_setapprole 'MyApplicationRole', 'MyPassword'" cmd.CommandTimeout = 90 cmd.Execute Set cmd = Nothing '4. refresh the connection string on the linked tables using an ODBC connection string Dim db As Database, td As TableDef, sConnect As String sConnect = "ODBC;DRIVER=SQL Server;SERVER=MyServerName;DATABASE=MyDatabaseName;Trusted_Connection=Yes" Set db = CurrentDb For Each td In db.TableDefs 'if a linked table Then If Left(td.Connect, 4) = "ODBC" Then 'update the connection string td.Connect = sConnect td.RefreshLink End If Next td Set td = Nothing '5. refresh the connection string in the pass-through queries Dim db As DAO.Database, q As DAO.QueryDef For Each q In db.QueryDefs If q.Type = dbQSQLPassThrough Then 'update the connection string q.Connect = sConnect End If Next Set q = Nothing Set db = Nothing

Erland Sommarskog 101K Reputation points MVP

2023-11-13T14:25:57.48+00:00

No, it will not work out. Application roles and connection pooling does not play well together. Here is a link that goes right into the middle of a longer article on my web site. But there is a section on application roles that you may find interesting: https://www.sommarskog.se/grantperm.html#interlude

Albert Kallal 4,651 Reputation points

2023-11-14T01:38:35.3266667+00:00

Well, we are mixing up several things here.

First up, yes, you can write code in VBA to check/test for role membership in a given SQL security group. That as I stated can work fine, and a simple store proc + pt query from VBA to test/check for the current user in a given role is not a problem. Thus, you could use that role test/check to control say what reports (or forms) a users opens.

As for connection pooling? Again, we mixing things up here in a huge way.

All, and I repeat ALL of my context and explain was that of using linked tables, and built in connections.

If you choose to create in code an ADO connection, then yes, you can have in code multiple connection's with different security rights, but that is VAST VAST different then the user + password caching system I was talking about here. Context here matters, and matters boatloads.

To save coding efforts, and the reason why we all use Access is to save time and effort. That means that for the most part, we going to use linked tables, and we NOT hand coding loading of data into forms.

And I don't think it makes sense to use un-bound forms and code out ADO inserts/updates etc. The reason is simple:

In say VB6, or event c#/vb.net? Well there are boatloads of wizards and tools that let you build and design and use disconnected data tables.

In Access, we in general don't adopt the above approach since none of the built in tools, wizards and tools supports disconnected data tables. So, if you go down that road, you wind up with the worst of both worlds (no wizards, and ALL OF YOUR form data events such as before update, after update etc. can't be used!!!). So, you don't have all those cool tools like other systems, and you lose all of the great features built into Access. In other words, don't go down that road, since you be using the wrong tool.

For example, here is how I code a pass-though query in VBA and Access:

Dim rstData As DAO.Recordset With CurrentDb.QueryDefs("qryPassR") .SQL = "exec dbo.GetHotels2 @City = 'Edmonton'" Set rstData = .OpenRecordset() End With rstData.MoveLast Debug.Print "Record count = " & rstData.RecordCount

Note VERY close how the above code DOES NOT have any messy connection string code.

So, the overall assumption here is that we are using linked tables (without a uid/password for security). And I can point out that the above code example used a saved connection string for the PT query, and once again, no uid/password exists in that connection string.

So, as a overall design approach here, even with SQL server, we continue to using bound forms, we continue to use linked tables, and thus we WILL be using the automatic cached uid/password feature of Access when doing the above.

However, if you going to create an ADO conneciton object, and create a command object and query in ADO and VBA code? Then yes, you can certainly then have multiple connections with different rights, and such connections are not cached.

However, if you use ADO against linked tables, then yes, you will find the caching I speak of will become involved.

So, context here is VERY important.

When I spoke of the user + password caching, I was in the limited context of linked tables, and that of each table having a different connection string for that linked table. In this context, then NO you can't use different security for each linked table due to the connection caching system I speak of.

Given that the vast majority of Access applications (even with SQL server) will use linked tables, then as a "general narrative" then caching of connections and passwords occurs.

I suppose if one was to go 100% ADO and disconnected recordsets, then you could achieve separate connections with different rights.

And last but not least? The test/checking for role membership by a given user is a VAST different concept then that of having several tables linked with different connections to the same database (of which I pointed out can't work).

So, rather easy to test for membership in a given SQL server security role, but that as noted is different then having multiple active connections (linked tables) active...

Albert Kallal 4,651 Reputation points

2023-11-14T01:48:57.9+00:00

I will also add one more context to the above post. The concept of "application" roles is a 100% DIFFERENT issue then that of having multiple live connection strings each with a different user and hence that of each user having different rights (at noted, that can't work in Access with linked tables --- with ADO it can/could). However, using application role(s) with Access? Not tested that issue or concept at all, but as noted, that again is a 100% different context and issue then what been talked about here so far.

Greg Youdale 5 Reputation points

2023-11-14T04:04:28.13+00:00

Thanks Erland, that was a very interesting, deep and exhausting article about SQL security ... and thanks Albert for your discussion around linked tables and connections.

Albert - My .ade application took a very big hit when MS dropped .ade support and we had to revert back to .accdb format using linked tables.

There is a show stopper bug in Access, when using linked tables and triggers/merge replication on the SQL back end. When a new record is inserted into a linked table, Access incorrectly calls @@IDENTITY rather than SCOPE_IDENTITY() to get a reference to the record just inserted to refresh a bound form. In a replicated environment, the @@IDENTITY call returns the record ID of whatever the last record inserted was and many system tables are updated in a replication environment. Even a user defined trigger fired by the user insert, that inserts a record elsewhere, will cause the record ID returned to be from the trigger inserted record rather than the user inserted record. I raised an MS case, they agreed regarding the problem, but refused to fix it, citing that a fix may 'break' existing systems ... ha, existing systems can't be using it because it doesn't return the correct record ID ...

Suffice to say that my application does not use many linked tables ... rather, I have local Access tables that 'mirror' the SQL tables, and use a stored procedure to fill those tables, and stored procedures hooked up with the 'Before Update' and 'After Delete' events to handle the inserts, updates and deletes made to the local tables by the user ... actually works very well, just that I had to create 200+ stored procedures.

The ADO connection that is created in code (shown above) is the connection that is used to call the SQL Application Role and all subsequently called stored procedures ... and that seems to be working as expected, with the SQL object permissions as defined in the App Role.

The problem is in the separate ODBC connection that Access makes for the linked tables and pass-through queries ... it matters not whether connection pooling is in force or each linked table or pass-through query has a separate connection, the point is that this is at least a second connection, and has no 'knowledge' of the Application Role in the first connection and hence the Windows AD group that is used to access the SQL database must be given SELECT, INSERT, UPDATE and DELETE permissions on the linked tables to be able to carry out any user operation on those linked tables and EXECUTE on the stored procedures that are called through the pass-through queries.

A further gotcha is as noted by Erland and others, EXECUTE on a stored procedure that contains dynamic SQL will fail unless the user has permissions on the underlying tables in that dynamic SQL. So even my 'main' tables, where the user only needs EXECUTE on the stored procs that control operations on that table, are now not protected by the use of the Application Role ... the 'second' connection, with permissions purely dictated by the AD group permissions, now also requires at least SELECT on the 'main' tables to handle dynamic SQL contained in some stored procedures used for reporting (i.e. pivot tables defined in dynamic SQL).

I'm nearly back to simply giving SELECT, INSERT, UPDATE, DELETE and EXECUTE on all of the database objects to the Windows AD group ... and then there is nothing to stop users linking tables using MS Excel etc. outside of the application.

So, it seems that I can't win in any direction, and the users AD group pretty much requires SELECT, INSERT, UPDATE, DELETE and EXECUTE on all of the database objects ... back to where I was and not a great security place to be ...

Any final suggestions ...

Erland Sommarskog 101K Reputation points MVP

2023-11-14T05:30:57.9233333+00:00

A further gotcha is as noted by Erland and others, EXECUTE on a stored procedure that contains dynamic SQL will fail unless the user has permissions on the underlying tables in that dynamic SQL

There is a technique to avoid that, and that is actually the main topic of the article that I gave you a link to. But it will have to count as advanced. You sign the procedure with a certiticate, create a user from the certificate and then grant that user the SELECT permissions needed. Thereby you package the permissions with the procedure.

Albert Kallal 4,651 Reputation points

2023-11-15T20:27:10.6766667+00:00

Some final thoughts? For a migration, or using SQL server as the back end, then using linked tables, and DAO (not a type-o!!!) is the FAR BETTER choice. The only one exception I can suggest is the case of converting from a adp application. Since such a application will have used ADO code, then best to stick with that.

As a final note?

YES you can use with success sql server application roles, and they DO WORK! But, this ALL assumes my above context. That context is that you NEVER included the uid/password in ANY DAO code, and NEVER included the uid/password in ANY of the linked tables. This results in ONE cached logon, and THEN if you execute (activate) a sql application role, then this setup works.

However, my suggesting is not a whole lot of consolation when ADO and linked tables, since that means multiple connections are being used. Remember with suggested security approach, then if you open a access application (say hold down shift key - no start up code), and THEN attempt to open a linked table, it will NOT work!!! You as the linked article shows, must FIRST execute a SQL server logon. Once this is done, then all table links now will work!! (including pt queries).

Needless to say, this means you can have a different logged on user or prompt for a user logon at startup, and NOT have to re-link tables. As noted, this setup also works and allows use of SQL server application roles. In my testing, it does seem that some kind of "edit row" has to have occured. However, if I then execuate a role to making everything "read only" such as:

(from a pt query)

EXEC sys.sp_setapprole 'MyReadOnly4', 'zoo';

The application now indeed does become read only, and that includes any use of linked tables, VBA recordset code (DAO only!!!), and that even of calling stored procedures from a PT query as per above example code.

As for a few pointed out issues such as getting the "id" of a new record? We solved that by ensuring the form is dirty, and then we use this one liner:

me.dirty = false

The above forces a update, and that also gets us the autonumber pk values. However, since you have few linked tables, then adding a record BEFORE it is displayed/used in the form works quite well...

Greg Youdale 5 Reputation points

2023-12-18T02:31:15.57+00:00

Many thanks to Erland for his exhaustive article on application and database security solutions ... I considered the certificate approach too complex for the DBA to setup and maintain ... a friend of mine also mentioned certificate expiry issues, which I did not research further as we did not go down the certificate path ...
I am successfully using an SQL Application Role, called from our MS Access application to greatly improve the database security. In the Access application, I open a connection to the SQL database and call the SQL Application Role through that connection. That connection is maintained for the entire application session and, through the App Role, now carries all the permission required to execute the stored procedures that provide the functionality of the application. All users are members of an active directory group, and that group has no database permissions, hence those users cannot link to the database tables from outside of our application.
There is one complicating issue, however, that still proved to be no problem ... the application executes stored procedures to provide the pick list for all the combobox controls in the application, and Access passthrough queries are used as the recordsource for the combobox objects. The issue is that Access establishes its own database connection for each passthrough query, and that connection is separate from the connection where the Application Role was called. You can only supply a connection string for the passthrough query connection and you have no access to the connection object thus created. What this means is that the AD group must be granted execute permission on all those stored procedures referenced in the passthrough queries. This is still fine for security purposed as those stored procedures only return a SELECT list of lookup values ...
One further issue was in reporting from the application ... again, report data was provided by stored procedures executed via Access passthrough queries ... and hence, the AD group must also be granted execute permission on all those stored procedures referenced by the reporting stored procedures. The next issue came with using some dynamic SQL in some of the reporting stored procedures ... the AD group also now required SELECT permission on all tables referenced in the dynamic SQL.
It has all worked out well, and the members of the AD group ended up with only execute permissions on some stored procedures and SELECT permissions on some tables ... a very big improvement on the original select, insert, update and delete permissions on most of the database tables. I can sleep peacefully at night knowing that no user can upset the database by using Excel etc and linking to the database tables ...
As for connection pooling, we have seen no evidence or issues suggesting that the connection that calls the Application Role is being reused and causing issues ... and if the passthrough query connections are being reused, then that is just fine ...

Erland Sommarskog 101K Reputation points MVP

2023-12-18T21:57:03.3033333+00:00

a friend of mine also mentioned certificate expiry issues

For procedure signing, there is no check on certificate expiry.

through the App Role, now carries all the permission required to execute the stored procedures that provide the functionality of the application. All users are members of an active directory group, and that group has no database permissions, hence those users cannot link to the database tables from outside of our application.

Until they pick the application apart to find the password. The solution will protect you against the innocent "too smart for their own good" users, but not against the truly malicious users. For full protection, you would have to put the application on Terminal Server or Citrix, where users enter the application directly when they log in. Now there is no way they can get access to the approle password.

Albert Kallal 4,651 Reputation points

2023-12-18T22:11:58.46+00:00

Well, the PT query only going to use a different connection if you choose to do so. Look close at my posted example(s). Do you see ANY connection string at any point in time? (no, you do not!!). Once again, this means that the saved PT query is to use the SAME connection string as all the linked tables (it needs to be the same), and once AGAIN those linked tables do NOT include user id + password. The PT query connection is ALSO to NOT ever include the uid/password. Thus, then the pass word caching above I speak of MOST certainly does use the cached uid/password, and thus the application role against that PT query will also apply......

In other words, don't create a connection string in code for the PT query, but during linking of the table(s) to sql server, you also want to have that re-link code re-link the PT queries in question, and as noted, that PT query connection string does not, and will not have the uid/password in that connection....

Of course in your case, this issue not much of an issue, since you using domain (active directory) logons, and thus no user id/password ever existed in the connections anyway....

However, by not creating the PT query in code with a connection string, then even the PT query connection will become part of the cached connection, and thus the application role settings should apply....

Greg Youdale 5 Reputation points

2023-12-18T23:32:34.2+00:00

Thanks Albert ... I can't quite follow your discussion ... in the application, I physically create a connection object to the database. The passthrough queries and linked tables know nothing of this connection. It is through this connection that the Application Role is called, and hence the passthrough queries and linked tables know nothing of the Application Role either.
The passthrough queries and linked tables are assigned a connection string using integrated security, thus it is the permissions assigned to the active directory group who logged on, that pertain to these connection(s) ... I have no object access to these connection(s) and hence can't call the Application Role through these connection(s).
It is proven in use ... the Application Role has pretty much full rights to every database object ... while the active directory group only has permissions on the stored procedures called in passthrough queries and also permission on the linked tables ... as soon as I remove a permission from the AD group, calling that stored procedure via a passthrough query or linking that specific table fails ...
Sign in to comment