Preventing login to server from a client PC (1 Viewer)

[KitaYama]

Studying and learning Sql server.

Environment : Windows domain with a sql server machine.

The FE on all client PCs can access the data on sql server using DSNless linked tables. (Trusted_Connection)

Anyone from a client PC, can install SQL server management studio (or something else) and login to server.
I'm trying to learn how to disable users login to server, while they still are able to access the data from FE.

In SMS under server-->Logins I have this:

##MS_PolicyEventProcessingLogin##
##MS_PolicyTsqlExecutionLogin##
BUILTIN\Users
Domain\Administrator
NT AUTHORITY\SYSTEM
NT Service\MSSQL$SQLEXPRESS
NT SERVICE\SQLTELEMETRY$SQLEXPRESS
NT SERVICE\SQLWriter
NT SERVICE\Winmgmt
sa

When I open BULTIN\Users property --> Status tab I have this:

But still users can login to server from a client PC by MSM.
How can I prevent users login to sql server ? (except Domain\Admin or sa)

thanks.

 

[Minty]

This is going back a bit I'm afraid, so its all rather sketchy...
From memory you need to add each domain user as a user something like

DOMAIN\UserName

matching their windows credentials, you then point their login to that, and you can then set those users permissions accordingly.
I no longer have access to the older servers to be able to check exactly how it was set up, but it was along those lines.

 

[KitaYama]

Even if what you explained solves the problem, I doubt if it's the correct way to do it.
Imagine an organization with hundreds or thousands domain users and client PCs. Adding all those accounts and prevent them one by one doesn't seem to be the correct way.
And how about when domain admin adds a new user to the DC? Should he report to sql server admin too?

 

[Josef P.]

They do not have to be individual AD users. You can also give AD groups the rights.
Access to server must of course not be possible without allowed AD group membership.

Note: sql user 'sa' is always deactivated in my servers, AD admins or an explicit SQL-Server-Admin AD User are entered as serveradmins.

 

[KitaYama]

I added a domain user to the list of Logins in sql server : Server --> Security --> Logins
DomainName\Username

With above setting, When I try to open the FE on a PC logged in with that domain account, I receive 18470 error
"[Microsoft][ODBC_SQL Server Driver][SQL SERVER] login failed for User DomainName\Username
Reason: The account is disabled"

If I set login to Enabled in above image, FE opens without any error.

Am I doing something wrong here?

thanks.

 

[GPGeorge]

I don't think that would solve the problem because it doesn't limit the user to one application, i.e. Access, which is why the problem exists in the first place.

What we need is "Application-based Security" for Access in SQL Server. That is, the ability to designate that only specific applications can use those credentials.

I doubt that exists, does it?

========

Sorry. I intended for this to be posted some time ago, but apparently the "Post Reply" only works if you click on it.

 

[Josef P.]

What we need is "Application-based Security" for Access in SQL Server.

Application Roles - SQL Server

Use application roles to enable access to data only for those users who connect through a specific application in SQL Server.

learn.microsoft.com

If I set login to Enabled in above image, FE opens without any error.

If you have not set any rights for this user in the database, you have completely enabled access to the database somewhere.

 

[GPGeorge]

Application Roles - SQL Server

Use application roles to enable access to data only for those users who connect through a specific application in SQL Server.

learn.microsoft.com

If you have not set any rights for this user in the database, you have completely enabled access to the database somewhere.

Do you know if Access honors SQL Server's application roles? I've not seen that explained, but if it does, that would be awesome.

 

[Josef P.]

You can already use it.
However, you must ensure that the ODBC connection with which you executed sp_setapprole remains open.

Just tested again: it also works for linked tables.

Test scenario:
SQL user with read-only access
Application role with write permissions.

The data in the linked table cannot be edited before set_approle is executed.
Once the AppRole has been set, the data can be changed.

I have no practical experience with this.

/edit:
If I were to use this in a client, I would probably use ADODB and bind the forms to ADODB.Recordsets.
This gives me more control over the connection.

 

[KitaYama]

If you have not set any rights for this user in the database, you have completely enabled access to the database somewhere.

Where's the most likely place to look for?

Let me take back a step.
Do you know any article, site or book that explains the correct method and settings for adding a database to sql server? The objective is users are able to access the data from FE, but can not login to server with SMS. I may have had changed some settings/rights somewhere.

thanks.

 

[Josef P.]

The objective is users are able to access the data from FE, but can not login to server with SMS.

Then you will have to use application roles.

Why are users only allowed to access the data via the FE?
What "security" do you achieve by doing this? We have already discussed the fact that an access FE does not offer a high level of protection.
I always protect my data on the SQL server side, so I don't care how the users access the data.

 

[KitaYama]

Then you will have to use application roles.

Why are users only allowed to access the data via the FE?
What "security" do you achieve by doing this? We have already discussed the fact that an access FE does not offer a high level of protection.
I always protect my data on the SQL server side, so I don't care how the users access the data.

I'm sorry. I may have not expressed myself well.
I'm trying to learn how to protect data on sql server side, not FE side.
For now I don't care about any protection on FE side.

I was looking for settings on server side to achieve this goal:
From a Windows client PC, nobody should be able to login to sql server using SMS (or other tools) except one user. While anybody can use FE to add/edit data.

Thanks again.

 

[The_Doc_Man]

I believe part of this is to be aware that there are "intrinsic" rights identifiers associated with how you login. For instance, you need permission for "Interactive" to start a session on the server. If you tried to login but your rights list had "Interactive" set to DENY then you would not be allowed to log in. Perhaps you would need to set "Service" or "Network" permission to ALLOW. It has been eight years since I played with this.

Administrative tools and logon types reference - Windows Server

Identify the risk of credential exposure associated with different administrative tools

learn.microsoft.com

This article is your starting point, I think. The question will be whether you need INTERACTIVE logon mode to be allowed for ODBC connections.

 

[KitaYama]

I believe part of this is to be aware that there are "intrinsic" rights identifiers associated with how you login. For instance, you need permission for "Interactive" to start a session on the server. If you tried to login but your rights list had "Interactive" set to DENY then you would not be allowed to log in. Perhaps you would need to set "Service" or "Network" permission to ALLOW. It has been eight years since I played with this.

Administrative tools and logon types reference - Windows Server

Identify the risk of credential exposure associated with different administrative tools

learn.microsoft.com

This article is your starting point, I think. The question will be whether you need INTERACTIVE logon mode to be allowed for ODBC connections.

Thanks. I'll take a look.

Edit : I read the article and I admit I didn't understand it well. To me, it's about services on DC (domain controller) and login to sql server (in my opinion) isn't DC's job. Sql Server decides who can login and who can't. Who can use the data and who is not allowed.

 

[Pat Hartman]

I'm trying to learn how to disable users login to server, while they still are able to access the data from FE.

I solved this problem a long time ago. If you do NOT use Windows authentication to log in to your server, you can prevent users from logging directly in using SSMA and also from linking using a different Access FE. They never know their login password because the app calculates it on the fly.

1. Create a method where you "calculate" a password based on user specific information. Use their Login name, their employeeID, their HD serial number, whatever, Pick some characters from all of the above, randomize them. The point is, you need to be able to generate a password that is never saved.
2. When the user logs into your Access app, you use your "calculation" to come up with the password that goes with the login name.
3. Only you and the DBA know what the password is. You generate it when you add a new user and give it to the DBA.
4. An alternative, is to use ONE password for every login. This keeps the separate login per user which makes it possible to track individual activity but is pretty unsecure since the password needs to be hardcoded in the app.

Should something that you use in your algorithm that calculates the password, changes, you will need to generate a new password based on the change and give it to the DBA to reset the user's password.

 

[KitaYama]

I solved this problem a long time ago. If you do NOT use Windows authentication to log in to your server, you can prevent users from logging directly in using SSMA and also from linking using a different Access FE. They never know their login password because the app calculates it on the fly.

@Pat Hartman Thanks for sharing your experience.
I just want to be sure I'm correct.
If I understand you correctly, the first step is setting sql server not to use windows authentication for logins.
Then

1. Create a method where you "calculate" a password based on user specific information. Use their Login name, their employeeID, their HD serial number, whatever, Pick some characters from all of the above, randomize them. The point is, you need to be able to generate a password that is never saved.
2. When the user logs into your Access app, you use your "calculation" to come up with the password that goes with the login name.
3. Only you and the DBA know what the password is. You generate it when you add a new user and give it to the DBA.

It's easy to be done. No problem here.
But what's the next steps? In FE, Do I have to create a connection string that uses the password and relink all tables with this connection string?
What happens when a new version of FE is released? On first run of each new release I have to relink tables with user's calculated password?

Thanks again for your time.

 

[Pat Hartman]

When the user logs in to the application, you log in to the server using the user's login name + the generated password. Then those are the credentials that get used. So, I think all you need to do is to change your connection string. I don't have any of the databases where I implemented this. It was quite a while ago. Most of the time, the users just don't have the technical ability to circumvent the application and go directly to the server but for this particular client, the users were engineers who had the technical knowledge so we stopped the "leak" by hiding their credentials from them.

 

[KitaYama]

When the user logs in to the application, you log in to the server using the user's login name + the generated password. Then those are the credentials that get used. So, I think all you need to do is to change your connection string. I don't have any of the databases where I implemented this. It was quite a while ago. Most of the time, the users just don't have the technical ability to circumvent the application and go directly to the server but for this particular client, the users were engineers who had the technical knowledge so we stopped the "leak" by hiding their credentials from them.

Million thanks for your time and help.
I think I have something now to start with.

 

[WayneRyan]

KitaYama,

Google SQL Server logon triggers ... that's what you need.

Hth,
Wayne

 

[cheekybuddha]

It's certainly easier to restrict access by IP address in servers like MySQL, but you can do it with triggers/firewall rules in SQLServer.

See search results