Windows Authentication Restriction
-
Hi.
I developped many application in Visual Basic 6.0 that access a SQL Server 7.0. All these application are programming with a connection string that use the Windows Authentication.
I have a problem with the Windows Authentication.
How can I block all other application that I don't want to access SQL Server ??
For exemple, if a user have an application that I develop on his computer and another one that he download on the internet (for exemple) and that application give him the opportunity to access my SQL Server, how can I block this application if it use also Windows Authentication.
I don't want that the application that the user download have the same right on the server that my application give him.
Right:
My application: Update in table tblTable1 = YES
Application downloaded: Update in table tblTable1 = NO
You know that the application that I developped and the downloaded application will have the same right on my SQL Server if they use the Windows Authentication.
I just want to block the second application.
I don't know if you understand ........
Thanks...
Jonathan
-
How is this any different to using SQL Server authentication?
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
-
Hmm. There has been some articles here about Application Roles. But basically it comes down to how you have secured your server.
In good security practice, users should not have INSERT, SELECT, UPDATE, DELETE permissions on any tables. All data access should be done though stored procedures. There are many reasons for this, including flexibility, that make this a much better way to do things. Also most downloaded programs will have no idea on how to use your stored procedures, and if they do, then your stored procedure will limit their damage.
For example, if you allow updates on your table to a user, what's stopping them from using any sql/ado application to run UPDATE Produce SET Price = '123' and thus changing the data for ever row in a table? If you have a sproc that was as simple as:
CREATE SPROC UpdateProducePrice
@ProduceID int
@Price decimal(9,3)
AS
UPDATE Produce Set Price = @Price WHERE ProduceID = isnull(@ProduceID,0)
GO
You'd then have limited the person to being able to change only one users password at a time.
Julian Kuiters
juliankuiters.id.au -
Thanks Julian for your information.
But, do you have another way to secure Windows Authentication from other application ?
For exemple registy, NTFS, .......
Normaly I place Insert, Update and Delete statement in stored procedure when I have more than one statement to be execute. When I have only one, sometimes, I execute them directly in VB Code depending of the application ?
If it's not possible, I will use SQL Authentication. With that, a downloaded application will need to obtain the password to be able to access my SQL Server.
But in this case, if it's possible, I prefer to forget this eventuality.
If you have others suggestion, I will appreciate.
Thanks
Jonathan
-
The issue here is how is the login and authentication being done? It appears the user is typing in his/her login and password (via Windows account). Once you give a user permission to access data, they have that permission regardless of what application they use. This is even true of SQL Server authentication.
The solution is to 'build' the login/password into the application itself. That way, when the application is started it passes the authentication data on to SQL Server. Then you can only add the login/password to the applications that you approve. In this case the user doesn't know the login/password and can't use it with other applications.
Or you can get management to approve a policy that won't allow 'unathorized' software.
-SQLBill
-
Finally, it's more secure to use SQL Authentication then Windows Authentication ?
For the Windows authentication, even if all the Insert, Update and Delete statement are in a stored procedure, the user can be able to execute the stored procedure if he found the parameter. In these case, I'm not early.
Thanks
Jonathan
-
As workaround and only as workaround you can analyze program_name column in sysprocesses table.
Run
select program_name
from sysprocesses
where spid = @@spID
You could check it in your triggers and if program_name = 'My_program' - continue, otherwise - do rollback.
(BTW, I do not know how you can set that 'My_program' name in your application.)
Again, it's just workaround. Please reconsider your security practice and read about Application Roles in BOL.
Igor
-
I have only skimmed this topic, so forgive me everybody if I've missed an excellent point (and expecially if it was yours), but ...
What SQL Bill says will work - you can authenticate the application, and let it authenticate users internally. But if you don't (or can't) do that, then look into application roles, as Julian suggested. I believe that they do exactly what you want. You create an application role with a password, and then your application activates the role at run time. Since the user does not know the application role's password, they cannot obtain the same permissions through their application.
I am simplifying here ... because I need to leave the office and go eat dinner! But read up on application roles - they are made for the very situation you are describing.
Cheers,
Chris
-
Hi.
I have some difficulty to understand the difference between Application Roles and a SQL Authentication. For exemple, if in my application I'm connecting to SQL with a SQL Login and these login are in a group that give him some right, what the difference with the Application role if the user don't know the password ??
For the Windows Authentication I understand why it's important to use an Application Role, but I can use also SQL Authentication to replace Application Role ?
I read about Application Role but I have some difficulty to see the difference between SQL Authentication if my user doesn't know the password.
If someone could help me to see the difference.
Thanks
Jonathan
-
It may be helpful to keep clear in your thinking the difference between authentication and authorization. Authentication is proving who you are (logging on to the SQL Server, in this case) and authorization involves the permissions that are granted to an authorized individual.
Whether a user is authenticated using SQL or Windows authentication does not affect the usefulness of application roles. The idea is the same in either case, so let's cook up a common quasi-example.
Suppose your application has the ability to edit sensitive data, but you only want people to be able to read (not edit) this data when they connect to the database without using your application.
To achieve this, you grant the users accounts (whether they are SQL- or Windows-authenticated accounts does not matter) the appropriate permissions to read the data. Then you create an application role (with password) and give it permissions to read and update the data. At runtime, you allow the user to authenticate to the server as usual, thus allowing them to read the data. However, when you want them to be able to update the data, your application can invoke the application role by calling sp_setapprole. Your application will provide a password to activate this role that the user does not know (best practice dictates that you store the password in an encrypted form and transmit it to SQL Server in an encrypted form also - sp_setapprole can encrypt the transmission part). This is sort of like authenticating the application itself - the password is sent to "prove" the identity of the application, not the user. That's why it's called an application role.
Once the application role is set for a connection, the user loses all their "normal" permissions, except for those associated with public (which are always active) and those associated with the application role that has been set.
Make sense? This is just a quick rundown of one way application roles might be used, but I hope it's helpful.
Cheers,
Chris
-
Another way to look at this (and I apologize for the double-post) is that if you use application roles, then SQL will take care of authenticating and authorizing both the user (via either SQL or Windows authentication) and the application (via application roles).
If, on the other hand, you use a SQL-authenticated application account (where the application always connects as the same login no matter who is really using the application) then the burden of authenticating and authorizing the user falls back into your code.
I know that some shops like to try to be "database-agnostic" when writing their code, but user authentication and rights management are a good example of the kind of thing you can exploit in a particular DB platform if you know the platform well enough. In this case, I have seen 3rd-party applications that we've installed that used application roles ... and in speaking to those developers I found out that the application roles were a really great thing for them. The puzzling thing is why people in shops that are utilizing other "proprietary" featurs of SQL Server haven't been more interested in adopting application roles (the reasons outlined in the first two paragraphs of this post seem particularly attractive to me).
That's all for now. I promise to resist the urge for a triple-post (even if the Olympic judges like that kind of thing).
Cheers,
Chris
-
Ok.
I understand now.
So I can give to my Windows login the right to select the data and to my application role the right to select, update, delete and insert.
So, my user will connect to SQL with a Windows Account but when the application need to be able to make an update, I will execute sp_setapprole.
Cool.
Thanks guys.
Jonathan
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply