| Author |
Topic |
|
qjam
Starting Member
19 Posts |
 Posted - 2006-05-12 : 00:42:32
|
| Hi all,My work is using a shared application which accesses a MSSQL 2000 database. To access the application, the folder on the Windows 2003 Server is shared and users can access the folder through a shared drive. For the application to access the database, it uses an ODBC connection to the MSSQL server which originally used the SA password. We have recently switched to using Windows Integrated Authentication because we believe it offers a higher level of security. However the only way in which we have been able to enable this is to add the windows users to the SQL server. The problem with this is that the application sets permissions for individual users on what records they can see within the database. We have found that by adding the windows users to the SQL Server, they can bypass the permissions the set by the application by simply using any application that can use an ODBC connection, such as Enterprise Manager, and see all the database.One way around this would be to set up domains of users with access privileges to the tables which reflect the permissions set by the application, and configuring a view of the data so they may only see the records that they have permissions to. However to do this would require a high administrative cost to ensure that changes made in the application are reflected in the privileges of the SQL server.Instead, is there a way the SQL server can authenticate that the ODBC connection is coming from the correct application using Windows Integrated Authentication?This would allow the applcation to determine security, and stop users from connecting to the SQL server using other applications.Alternatively, can the SQL server, using Windows Integrated Authentication, also ask the application to supply a username and password?Any help with this matter would be greatly appreciated.Thanks! |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-05-12 : 01:39:38
|
| I would have the application log in with an application-specific UserID/password, and then have the application log the user in (i.e. a User/Password table in the application database).Sounds like you are not using Stored Procedures? Stored procedures would allow the users to have Execute permissions on the SProcs but no underlying permissions on the tables. That would be a way to allow integrated security.Kristen |
 |
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2006-05-12 : 03:09:53
|
| I have a similar issue to this - we are trying to badger the application supplier into making use of Stored Procedures but it's a long, slow, tedious business. In our case we can add new stored procedures etc. so we do that "properly" and assign access to groups of users, we also try to make these procedures produce output that is more valuable to the users so that they will prefer to user them. We are also in the process of setting up a reporting database which we export data overnight too and again we do that "properly". Finally make sure that the Guest account is disabledSteve-----------Oh, so they have internet on computers now! |
|
|
|
qjam
Starting Member
19 Posts |
Posted - 2006-05-14 : 21:10:24
|
| Hi all,I would love to have control over the application and do it and a more securely, but we didn't create the application, and trying to get the vendor to do it is like pulling teeth.What I'm really after is the best security configuration for the application which will provide the highest level of security, and more importantly please my manager :)The only things we have control over are whether the application uses the SQL Password or Windows Authentication, and who can access the folder with the application in it.When using the SQL Password, the application can only log into the database using the one account, which basically must have rights to do everything.Using Window Authentication, I have to add individual users to the SQL Server to allow the application to access the database when they are using the application. However, this will allow the user to use other applications to access the server, since they have rights to it.What do you think is the best configuration?Thanks. |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-05-15 : 01:21:09
|
| not sure if this applies to you...i had this one confidential database that developers have access to and they kinda abused the credentials they haveso i set up a job that scans the sysprocesses table and kills the spid connecting to the database using a different application and machine (installed on another machine) running every 1 minute, the dev couldn't complain coz they were not supposed to connect to the database anyways using any other appsor you can use the sql login and ask your vendor to create a dll for you to use, atleast the dll can encrypt the credentials unlike with an ini fileHTH--------------------keeping it simple... |
|
|
|
qjam
Starting Member
19 Posts |
Posted - 2006-05-15 : 03:26:14
|
| Interesting idea, I don't know how much my manager will like it, but it's worth some consideration.I've actually never heard of 'sysprocess' table until now. Would there be a way of using this table to authenticate the application trying to connect to the database, or restrict the network address?Perhaps through a stored procedure which is triggered when an app logs in? I don't really have any experience with this so I probably don't know what I'm talking about.I would really like to restrict access to the database to only the application we are using. It seems like an easier solution, although it may not be all that secure. |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-05-15 : 03:43:54
|
it's a system table, master..sysprocessesi tried adding a trigger but failed (coz it probably is not allowed) so i opted for the job insteadcheck it out and see what works for youbut if your case is about really tightening security, easiest is to setup a dedicated server for that apps  --------------------keeping it simple... |
|
|
|
Jim77
Constraint Violating Yak Guru
440 Posts |
Posted - 2006-05-16 : 09:42:13
|
What about removing all users from the users folder and only allowing them to access the application through a dedicated application role ???(But this could mean a redesign of the whole app!!!)Chheck out application roles on B.O.L. |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-05-16 : 09:55:39
|
| the application role can also be invoked, unless he embeds it in the apps --------------------keeping it simple... |
|
|
|
qjam
Starting Member
19 Posts |
Posted - 2006-05-16 : 19:29:23
|
| I'm sorry, I'm new to this. What is B.O.L. and how do I find it?Thanks! |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-05-16 : 21:09:31
|
bol is books online, it comes with your installation cd, it's the sql server help fileyou can also use msdn site and search from there if you don't have the cd or download from there --------------------keeping it simple... |
|
|
|
qjam
Starting Member
19 Posts |
Posted - 2006-05-17 : 21:39:48
|
| I've had a look at this, sounds very interesting. It sounds like it would certainly solve the problem, however, as Jim77 said, it could require a total redesign of the app.How does the application actually use this app role? Does it need to run a stored procedure every time it uses, say in VB.NET, the OleDBConnection.Open(), or does the stored procedure need to be run just once when the application is first executed?If it was just the once, then I could probably get the vendor to do it, but if it was more, then it would be very unlikely.thanks. |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2006-05-17 : 23:24:09
|
| the approle is used to apply permissions on an account, not used to connect, in a nutshell this is how it works (read details in bol)...1. connect to server\database using usual credentials (win or sql account)2. execute sp_setapprole to invoke approle and restrict permissions from the login used in #1caution: do not invoke it again with another connection, you need to close the current connection then open a new one and invoke it againwhile in effect, it will not allow you to do cross-database query conn1-->conn2HTH--------------------keeping it simple... |
|
|
|
|
Authenticating an application