login security question

Hi,
I have an application which connects to the SQL server. We have several user
s logging into this application. All of their user-id, passwords are validat
ed and converted to an owner profile, which is then used throughout the appl
ication.
My problem is, this owner profile should be prevented from accessing the dat
abase directly using Enterprise Manager or Query Analyser. The database shou
ld be accessible only from the application for this owner/global profile.
How do I go about achieving this. The application was set up like this by a
person long time back who is not with us anymore. Also, I do not know SQL Se
rver Administration. So, please detail out what information I have to look u
p and what steps I will hav
e to follow.
Thank you in advance.
SunnyLook into SQL Server Books online for "application roles" topic. This
explains about how to create and activate an application role within your
program and use it. This may require some code changes.
HTH
Prasad Koukuntla
"Sunanda" <Sunny@.discussions.microsoft.com> wrote in message
news:87CB3205-C041-4F23-AC6E-7BF23E7AB2C6@.microsoft.com...
> Hi,
> I have an application which connects to the SQL server. We have several
users logging into this application. All of their user-id, passwords are
validated and converted to an owner profile, which is then used throughout
the application.
> My problem is, this owner profile should be prevented from accessing the
database directly using Enterprise Manager or Query Analyser. The database
should be accessible only from the application for this owner/global
profile.
> How do I go about achieving this. The application was set up like this by
a person long time back who is not with us anymore. Also, I do not know SQL
Server Administration. So, please detail out what information I have to look
up and what steps I will have to follow.
> Thank you in advance.
> Sunny|||In my opinion, that does not seem possible. SQL doesn't know what CLIENT TO
OL is touching it. If the "connection" from the client application comes in
through a username/password, then that username/password has access to SELE
CT, UPDATE, DELETE, etc fro
m tables.
That is why we do all our database access through STORED PROCEDURES - so act
ual table access is not possible. Granted, the users can still call STORED
PROCEDURES from the EM and QA tools, but that is less likely to happen.
Can you hide the "connection" username/password from the users?
"Sunanda" wrote:

> Hi,
> I have an application which connects to the SQL server. We have several us
ers logging into this application. All of their user-id, passwords are valid
ated and converted to an owner profile, which is then used throughout the ap
plication.
> My problem is, this owner profile should be prevented from accessing the d
atabase directly using Enterprise Manager or Query Analyser. The database sh
ould be accessible only from the application for this owner/global profile.
> How do I go about achieving this. The application was set up like this by a person
long time back who is not with us anymore. Also, I do not know SQL Server Administr
ation. So, please detail out what information I have to look up and what steps I wil
l h
ave to follow.
> Thank you in advance.
> Sunny|||Steve,
No the connection profile is alreay know to the users, that is why we would
like to prevent users from using that in the enterprise manager to make chan
ges.
Please let me know if there are any options.
Thanks a lot.
Sunny
"Steve Z" wrote:

> In my opinion, that does not seem possible. SQL doesn't know what CLIENT TOOL is
touching it. If the "connection" from the client application comes in through a use
rname/password, then that username/password has access to SELECT, UPDATE, DELETE, et
c f
rom tables.[vbcol=seagreen]
> That is why we do all our database access through STORED PROCEDURES - so a
ctual table access is not possible. Granted, the users can still call STORE
D PROCEDURES from the EM and QA tools, but that is less likely to happen.
> Can you hide the "connection" username/password from the users?
> "Sunanda" wrote:
>
have to follow.[vbcol=seagreen]|||"Sunanda" <Sunny@.discussions.microsoft.com> wrote in message
news:87CB3205-C041-4F23-AC6E-7BF23E7AB2C6@.microsoft.com...
> Hi,
> I have an application which connects to the SQL server. We have several
users logging into this application. All of their user-id, passwords are
validated and converted to an owner profile, which is then used throughout
the application.
> My problem is, this owner profile should be prevented from accessing the
database directly using Enterprise Manager or Query Analyser. The database
should be accessible only from the application for this owner/global
profile.
> How do I go about achieving this. The application was set up like this by
a person long time back who is not with us anymore. Also, I do not know SQL
Server Administration. So, please detail out what information I have to look
up and what steps I will have to follow.
If you can alter the code in the client application, you can use application
roles.
1) Use Enterprise Manager to access the database / roles. New Role. click
the Application Role radio button and give it a nice secure, obscure
password.
2) Give the Application Role the appropriate permissions.
3) Revoke the users' permissions
4) in the code of the application, put in a call to a stored procedure
called (I think, from memory) sp_setAppRole (F1 for application role to see
what the stored proc is called) using the secret password for the App Role
(which you don't share with the end users).
Now your users will have the appropriate permissions when using your app,
but not when using QA or any other app.
On an entirely different tack, you can try Group Policies. Use a GP to tie
down their desktop so that they are not allowed to run Quey Analyzer or
Enterprise Manager.
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.711 / Virus Database: 467 - Release Date: 25/06/2004|||Bob,
Thanks for your reply. I kinda understand this Application Role approach. Co
uld you please explain the following:
1. Say the password for the Application role is found, can a user access the
database through the Query Analyser or Enterprise Manager using the applica
tion rolde/password.
2. At present the application tracks the user who makes the changes to the d
atabase through the application. The application passes the userid to the st
ored procedures. But if I put in a Applciation role in between, will I still
have the actual userid to
track who actually did the inserts and updates through the front-end.
Thanks in advance,
Sunanda.
"Bob Simms" wrote:

> "Sunanda" <Sunny@.discussions.microsoft.com> wrote in message
> news:87CB3205-C041-4F23-AC6E-7BF23E7AB2C6@.microsoft.com...
> users logging into this application. All of their user-id, passwords are
> validated and converted to an owner profile, which is then used throughout
> the application.
> database directly using Enterprise Manager or Query Analyser. The database
> should be accessible only from the application for this owner/global
> profile.
> a person long time back who is not with us anymore. Also, I do not know SQ
L
> Server Administration. So, please detail out what information I have to lo
ok
> up and what steps I will have to follow.
> If you can alter the code in the client application, you can use applicati
on
> roles.
> 1) Use Enterprise Manager to access the database / roles. New Role. clic
k
> the Application Role radio button and give it a nice secure, obscure
> password.
> 2) Give the Application Role the appropriate permissions.
> 3) Revoke the users' permissions
> 4) in the code of the application, put in a call to a stored procedure
> called (I think, from memory) sp_setAppRole (F1 for application role to s
ee
> what the stored proc is called) using the secret password for the App Role
> (which you don't share with the end users).
> Now your users will have the appropriate permissions when using your app,
> but not when using QA or any other app.
> On an entirely different tack, you can try Group Policies. Use a GP to ti
e
> down their desktop so that they are not allowed to run Quey Analyzer or
> Enterprise Manager.
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.711 / Virus Database: 467 - Release Date: 25/06/2004
>
>|||<<1. Say the password for the Application role is found, can a user access t
he database through the
Query Analyser or Enterprise Manager using the application rolde/password.>>
Yes.
<<2. At present the application tracks the user who makes the changes to the
database through the
application. The application passes the userid to the stored procedures. But
if I put in a
Applciation role in between, will I still have the actual userid to track wh
o actually did the
inserts and updates through the front-end.>>
Yes. You can see the login id for the users, and you can use the SYSTEM_USER
function in, for
example, a trigger to get the login name. The user name, however, will be th
e app role name.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sunanda" <Sunanda@.discussions.microsoft.com> wrote in message
news:00D6F5D5-0BC3-43A4-A313-649B5E2E215C@.microsoft.com...
> Bob,
> Thanks for your reply. I kinda understand this Application Role approach. Could yo
u please explain
the following:
> 1. Say the password for the Application role is found, can a user access the datab
ase through the
Query Analyser or Enterprise Manager using the application rolde/password.
> 2. At present the application tracks the user who makes the changes to the databas
e through the
application. The application passes the userid to the stored procedures. But
if I put in a
Applciation role in between, will I still have the actual userid to track wh
o actually did the
inserts and updates through the front-end.[vbcol=seagreen]
> Thanks in advance,
> Sunanda.
>
> "Bob Simms" wrote:
>|||Thanks for your reply.
But can't the application role be restricted from using the QA/EM? This is n
ot a completely secure method. Is there any alternative.
Thanks,
sunanda.
"Tibor Karaszi" wrote:

> <<1. Say the password for the Application role is found, can a user access
the database through the
> Query Analyser or Enterprise Manager using the application rolde/password.
>>
> Yes.
>
> <<2. At present the application tracks the user who makes the changes to t
he database through the
> application. The application passes the userid to the stored procedures. B
ut if I put in a
> Applciation role in between, will I still have the actual userid to track
who actually did the
> inserts and updates through the front-end.>>
> Yes. You can see the login id for the users, and you can use the SYSTEM_US
ER function in, for
> example, a trigger to get the login name. The user name, however, will be
the app role name.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Sunanda" <Sunanda@.discussions.microsoft.com> wrote in message
> news:00D6F5D5-0BC3-43A4-A313-649B5E2E215C@.microsoft.com...
> the following:
> Query Analyser or Enterprise Manager using the application rolde/password.
> application. The application passes the userid to the stored procedures. B
ut if I put in a
> Applciation role in between, will I still have the actual userid to track
who actually did the
> inserts and updates through the front-end.
>
>|||> But can't the application role be restricted from using the QA/EM?
No, that is not the way it work. You need to protect the password. Why do yo
u say it is not a secure method?
Are you afraid of network sniffing? There's an encryption option in sp_setap
prole.

> Is there any alternative.
I don't know what your requirements are, as I haven't read the full thread.
App roles is a nice feature for
what it is performing. Other options includes app logins using a special pas
sword (but all users will use the
same logins), app uses stored procedures and views to access data...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sunanda" <Sunanda@.discussions.microsoft.com> wrote in message
news:CA998A64-A51E-42B4-9B8D-E06C37624B65@.microsoft.com...
> Thanks for your reply.
> But can't the application role be restricted from using the QA/EM? This is not a c
ompletely secure method.
Is there any alternative.[vbcol=seagreen]
> Thanks,
> sunanda.
>
> "Tibor Karaszi" wrote:
>