August 22, 2004 at 3:19 am
Dear DB Guru's,
I have a VB application with Sqlserver 2000 as backend .
Users of my app. is connected to the server by windows 2000 authentication mode.
Now i came to see that some of the users have installed Sql server in their PC and they are able to connect to my App Server directly through Query analyser.
How can i deny them access to my App server.
Thanks,
Have a nice day,
Shabu
August 22, 2004 at 7:04 pm
One option would be to deny their net logins access to the database (or server) -hopefully your network is using groups to contain the various users. Then create an application role (perhaps with its own network credentials) for access to the database - using sProcs and views to contain the users' access, not giving them direct access to the tables, etc.
Check out bol about using Application Roles...
Good luck!
August 22, 2004 at 8:31 pm
There are a couple of issues with application roles. First, the application has to make a stored procedure call in order to activate the application role. Once that application role is activated, there's no going back for that particular connection. The second involves when you're dealing with connection pooling.
However, Arnie is right. That's about the only way you're going to be able to block your users' access to SQL Server if you're using Windows authentication.
K. Brian Kelley
@kbriankelley
August 23, 2004 at 9:07 am
Is the VB app conducting its work through Stored Procs or are you allowing for ad-hoc SQL to be run against the database?
Corie Curcillo
MCT, MCDBA, MCSD
August 23, 2004 at 8:18 pm
If the VB app only uses stored procs and/or views (a good practice for both security and performance), you can grant execute permission on the SPs or select on the views without granting permissions on the underlying tables. This way even if they do connect using other apps, they won't be able to access tables directly.
This assumes you have removed the "guest" user from the db and have implemented other security precautions. You might want to take a look on http://www.sqlsecurity.com at the checklist under FAQs and Lockdown under Tools.
Mike
August 24, 2004 at 4:15 am
Dear Corie,
I have provided 3 levels of security for my application through front end and it's working perfectly but if any of the user of my app has installed Sql Server on his PC, he or she is able to connect to all the DB servers running on the network using their domain user account.
I cannot deny access to my DB as they are the uses of my app.
August 24, 2004 at 7:33 am
What happens to the connection? Connection pooling takes place when the connection string is the same, which it will be when using windows authentication. I would guess the connection stays the same, once the approle has been activated, it just disregards the user's permissions. Could you please clearify the effects on connection pooling.
August 24, 2004 at 7:33 am
You are correct; you cannot deny access at the database level. As per Mike's previous suggestion, what we need to do is GRANT access at the database object level. You can however remove their User Accounts from any databases that they should not have access to.
Keep in mind; you have a bigger problem than just SQL Server tools on user desktops. Any user w/ MS Access installed (which is probably many) can also connect via linked tables and mess w/ the data.
Corie Curcillo
MCT, MCDBA, MCSD
August 24, 2004 at 7:53 am
You've hit the nail on the head. If you are using connection pooling and the login parameters are the same, once you activate an application role, it's active for that connection, period. Typically, if you watch connection pooling in action, you'll see an sp_reset_connection (I believe that's it) being issued every time that connection is re-used. However, the app role is maintained, even through that.
Now, if you have an application using Windows authentication to connect to SQL Server, and the account is the actual user's, you don't incur a lot of risk here. But if it's a service account and there is the potential for multiple app roles in use, then you do. It's just something to plan for.
K. Brian Kelley
@kbriankelley
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply