How secure SQL Server is? (1 Viewer)

KitaYama

Well-known member
Local time
Today, 19:21
Joined
Jan 6, 2022
Messages
1,489
Not that I have a problem, I'm just trying to figure out how things work.

Scenario:
Active Directory with a Windows Server 2016 machine as the Domain Controller
Sql Server Express 2019. Authentication is set to mixed mode.
Clients : Windows 11, 10
Access database FE has linked tables to sql server using Windows Authentication.

With this situation, if anyone from a client PC in active directory create an empty database, s/he can import linked tables from sql server and do whatever s/he wants with the data.

Is this normal? Is there anything that can be done to prevent unauthorized access to the tables?

Thank you.
 
Last edited:

GPGeorge

Grover Park George
Local time
Today, 03:21
Joined
Nov 25, 2004
Messages
1,775
Not that I have a problem, I'm just trying to figure out how things work.

Scenario:
Active Directory with a Windows Server 2016 machine as the Domain Controller
Sql Server Express 2019. Authentication is set to mixed mode.
Clients : Windows 11, 10
Access database FE has linked tables to sql server using Windows Authentication.

With this situation, if anyone from a client PC in active directory create an empty database, s/he can import linked tables from sql server and do whatever s/he wants with the data.

Is this normal? Is there anything that can be done to prevent unauthorized access to the tables?

Thank you.
You might get some insights from this recent AUG- Access Pacific Presentation by Anders Ebro.

Keep in mind, though, that if a person has permissions to use your data in any way, they have, by definition, the ability to misuse it also. So all you can do is limit who has permission. The scenario you describe presumes that the "anyone" does have those permissions, regardless of whether there happens to be an accdb linked to the SQL Server. Access itself is immaterial to the problem. It's the way permissions are set on the SQL Server and any databases on it that matter.
 

KitaYama

Well-known member
Local time
Today, 19:21
Joined
Jan 6, 2022
Messages
1,489
Thanks for the link.
Most of the thread is about the error message Access shows on disconnection and how to hide it to prevent users see the database name. But it really doesn't need the error message to find out the server name.
Anyone can run a IP scanner and find the IP of the server, start Access wizard for external data and type the IP. The wizard shows a list of all the databases available and he can connect to any of them.

Reading it all, seems nothing can be done. Pat Hartman's solution seems interesting, but I can't figure it out how she did it.
Galaxiom suggestion is too complicated and I'm not sure it will prevent Windows authenticated users accessing the database. I have to test it to see how it works.

@isladogs I've read most of your articles about securing Access databases. It gives me the impression that having a Access BE and hide it somewhere and follow your articles to secure BE & FE is much more secure than using sql server.

Thanks for taking your time and replying.
 

KitaYama

Well-known member
Local time
Today, 19:21
Joined
Jan 6, 2022
Messages
1,489
The scenario you describe presumes that the "anyone" does have those permissions, regardless of whether there happens to be an accdb linked to the SQL Server.
Well...it depends on how you look at the case. it's not my scenario. It's how Microsoft has set it up. I'd be grateful if someone explain a better way.
Anyone who can login to domain, is able to do whatever he likes with the data in sql server.

I'm in train now and can not watch your linked. I'll watch it as soon as I'm back home.
thanks
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:21
Joined
Oct 29, 2018
Messages
21,357
Couldn't you assign users to a group in AD and then only give access permission to the SQL Server instance to that group? Just curious...
 

cheekybuddha

AWF VIP
Local time
Today, 10:21
Joined
Jul 21, 2014
Messages
2,237
Anyone can run a IP scanner and find the IP of the server, start Access wizard for external data and type the IP. The wizard shows a list of all the databases available and he can connect to any of them.
Just because someone has an ActiveDirectory account does not mean they have been given permissions to access the server or its databases - only if the DBA has done a poor job setting things up!
 

isladogs

MVP / VIP
Local time
Today, 10:21
Joined
Jan 14, 2017
Messages
18,186
No. I would never claim that Access can ever be more secure than SQL Server. With care and planning Access databases can be made reasonably secure but, for example, it would never be acceptable to store credit card information in an Access database.

If I could only persuade MS to act upon my wish list for additional security in Access, it could be almost unbreakable.
 

KitaYama

Well-known member
Local time
Today, 19:21
Joined
Jan 6, 2022
Messages
1,489
Just because someone has an ActiveDirectory account does not mean they have been given permissions to access the server or its databases - only if the DBA has done a poor job setting things up!
SQL server is set up to accept windows authentication. If a user has an account in active directory and can login to a PC, doesn't it mean he can access the database? Is there anything else to set in sql server?
 

KitaYama

Well-known member
Local time
Today, 19:21
Joined
Jan 6, 2022
Messages
1,489
Couldn't you assign users to a group in AD and then only give access permission to the SQL Server instance to that group? Just curious...
Let me think about it and test to see how it comes out.
thanks.
 

cheekybuddha

AWF VIP
Local time
Today, 10:21
Joined
Jul 21, 2014
Messages
2,237
SQL server is set up to accept windows authentication
Permissions must still be granted to those users on which databases can be accessed, which tables within those databases can be accessed, which fields withing those tables can be accessed, and what level of access to those databases, tables and fields, eg SELECT, UPDATE, DELETE, EXEC ...
 

KitaYama

Well-known member
Local time
Today, 19:21
Joined
Jan 6, 2022
Messages
1,489
Permissions must still be granted to those users on which databases can be accessed, which tables within those databases can be accessed, which fields withing those tables can be accessed, and what level of access to those databases, tables and fields, eg SELECT, UPDATE, DELETE, EXEC ...
I know about it. Maybe I have to put it this way.
Listen. I have a user. He should be able to update - insert -delete data in a specific database. I create a FE with Microsoft Access and he can use the FE for his job.

But he can always open a new Access file and add linked tables from server to this new database. Because 1-he is a member of the domain. 2- He has the permission to update the table.

Now my question ; Is there any permission in SQL Server that allows a user update/insert data, but not access the server itself? or prevent him from adding linked tables to FEs?
if Yes : I'd really like to learn how.
if No : Then securing FE has no meaning. Because how much I secure a FE, the user can always access the database via his own app.

I'm not insisting on anything. I'm just trying to understand how organizations secure their data.
Thank you.
 

GPGeorge

Grover Park George
Local time
Today, 03:21
Joined
Nov 25, 2004
Messages
1,775
Well...it depends on how you look at the case. it's not my scenario. It's how Microsoft has set it up. I'd be grateful if someone explain a better way.
Anyone who can login to domain, is able to do whatever he likes with the data in sql server.

I'm in train now and can not watch your linked. I'll watch it as soon as I'm back home.
thanks
I think perhaps you misunderstood my point. The MS Access has nothing to do with any user's ability to connect to a SQL Server and to any database on that server, or any table, view or stored procedure in that database. Permission on the SQL Server and in each database determine who can, and can't, get to the data.

"Anyone who can login to domain, is able to do whatever he likes with the data in sql server."

That is not true.

Any user can only get to the data in a given SQL Server instance--regardless of where it is located, and regardless of whether the user tries to connect via Windows Auth or SQL Server Auth--if the DBA for that SQL Server instance has already given that user account permissions to do so. either directly or through a group in which that user is a member . It's much more sophisticated than that, really, but step one is to have the DBA open the door for the user account.

Again, having MS Access installed and available is immaterial here. It's only one path to connect, IF the user has permissions to connect.
 

GPGeorge

Grover Park George
Local time
Today, 03:21
Joined
Nov 25, 2004
Messages
1,775
I know about it. Maybe I have to put it this way.
Listen. I have a user. He should be able to update - insert -delete data in a specific database. I create a FE with Microsoft Access and he can use the FE for his job.

But he can always open a new Access file and add linked tables from server to this new database. Because 1-he is a member of the domain. 2- He has the permission to update the table.

Now my question ; Is there any permission in SQL Server that allows a user update/insert data, but not access the server itself? or prevent him from adding linked tables to FEs?
if Yes : I'd really like to learn how.
if No : Then securing FE has no meaning. Because how much I secure a FE, the user can always access the database via his own app.

I'm not insisting on anything. I'm just trying to understand how organizations secure their data.
Thank you.
Ah, you just clarified the point by specifying that the user in question does already have permissions to use the database. That means you are right, he can get to the data in that database anytime he or she wants to do so, regardless of whether he uses the Access interface you provide, Excel, or any other method he can conceivably use. Permissions lie outside the MS Access environment.

You are NOT securing an FE here, you are securing the data in the database in SQL Server.

Unfortunately, this is a big problem for many Access developers in larger organizations. For a long time, many Access MVPs have lobbied Microsoft for the ability to connect to SQL Server via an Application Role (they exist in SQL Server, but not in Access). This would allow the developer to specify the Access FE, and not just Access itself, which is allowed to connect to a specific database, in addition to having the right user permissions. So far, it's not happened.
 

KitaYama

Well-known member
Local time
Today, 19:21
Joined
Jan 6, 2022
Messages
1,489
@GPGeorge Thanks for confirming and clearing the doubt. If it's been a problem, so I can do nothing about it.
Million thanks.
 

cheekybuddha

AWF VIP
Local time
Today, 10:21
Joined
Jul 21, 2014
Messages
2,237
so I can do nothing about it.
I'm still a bit confused.

Can you not get the DBA (is that you?) to create the proper Access Roles and have them assigned to the AD users so that they can only access what they're supposed to
 

KitaYama

Well-known member
Local time
Today, 19:21
Joined
Jan 6, 2022
Messages
1,489
I'm still a bit confused.

Can you not get the DBA (is that you?) to create the proper Access Roles and have them assigned to the AD users so that they can only access what they're supposed to
1- No I'm not the DBA. And I don't even know who is. Someone from IT I suppose.
2- Did you see #13 post?
 

GPGeorge

Grover Park George
Local time
Today, 03:21
Joined
Nov 25, 2004
Messages
1,775
I'm still a bit confused.

Can you not get the DBA (is that you?) to create the proper Access Roles and have them assigned to the AD users so that they can only access what they're supposed to
That's the key right there. SQL Server Database Roles can be very fine-grained, by row and by column if need be. Anders describes that in the video to which I linked.

However, when a user has those permissions, that user can connect using any method available -- Excel, a new-born Access accdb, etc.--and use those permissions outside the specific accdb FE provided to them. It's not really an "MS Access security" problem in that sense. Access just happens to be the route we follow most of the time.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:21
Joined
Oct 29, 2018
Messages
21,357
If it's been a problem, so I can do nothing about it.
I think this is where the confusion lies. If you intended to give a user permission to access the data, then it's not a problem, is it? I think the problem you're talking about lies outside of SQL Server or Access - the problem is with the user. If you cannot trust the user to not manipulate the data against company rules, then why give them permission at all. Things like this are more of an HR problem than an IT one.
 

Users who are viewing this thread

Top Bottom