September 5, 2002 at 4:01 am
How can I make isql connection not to work for a specific database user. The user should only be allowed to connect to database
using the application alone.
(Please take note that the user has its own installer that he do tries to install all components including isql).
doc
doc
September 5, 2002 at 5:01 am
One way would be to implement a group policy that prohibits running isql.exe for that user. Other than that, you could embed a sql login in your application so that when the user tries to connect, they have no access based on their NT login. The other way would be to use a NT login in combination with an app role. Again, user would not know th eapp role password and so would have no access. I say no access but it would depend on permissions granted to both the guest user and to the public role. Finally, you could check app_name() in a trigger to at least disallow updates/deletes made using ISQL.
Andy
September 6, 2002 at 9:59 am
There is no good way to do this. Andy has some suggestions that will work, but are a hassle.
Best idea would be to administratively handle this. Use auditing or a trigger to check and then let a manager deal with the person.
Steve Jones
September 7, 2002 at 12:23 am
There is no simple solution this could be the one way....
if you fire this query........
select spid,hostname,program_name from master..sysprocesses with(nolock) where hostname = ''
and program_name = 'program_name'
51,MachineName,SQL Query Analyzer
you will get this result if the user is connected....
you can make a procedure which fired automatically every minute and fire this command and if a result is found than kill that spid.....
Cheers,
Prakash
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
September 8, 2002 at 9:27 pm
It seems nearly every site has this problem. So does nearly every database!
The best way I have seen it solved is by double encrypting all sql-login passwords:
1) Build an application account creator/password changer that uses a specific encryption method. The method itself is fairly unimportant unless your security is very tight. When accounts are created and passwords changes, the chosen password is encrypted BEFORE being set inside SQL.
2) Build an application login routine that encrypts a typed password with the same method before connecting to the SQL server
This way, a user never actually knows their sql password and cannot log on to iSQL, msQuery or through other potentially dangerous back doors.
This may sound like a hassle, but it really is pretty simple. All you need is a consistant two way encryption routine.
I have an example for WINNT/2k of a vb class module that uses the CryptoAPI (CSPs) if you like.
bb
September 9, 2002 at 1:26 am
Dear Eveybody,
I would like to give my best appreciation to all who answered by query. It really did help.. Thanks
Felix
doc
doc
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply