Any idea why the following dmo connection string would not work on MSDE, but
does work on SQL Personal Edition? It appears to connects, but then can't
find the server name. In both cases we are using the latest SQL Personal,
MSDE, and Windows XP.
Thanks for any guidance.
Neil
connection string:
server=myserver;trusted_connection="yes";database= northwind;connection
timeout=10;
exception:
[INFO] 20040517 17:59:34.598 (myControl) Connected successfully to
server=VPC1W2KSQL\VPC1MSDE;Trusted_Connection=yes; database=master;connection
timeout=10;
[EXCEPTION] 20040517 17:59:51.352 (SQLServer) Cannot connect
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or
access denied.
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).
at SQLDMO.SQLServerClass.Connect(Object ServerName, Object Login, Object
Password)
at SQLServer.Connect()
Was MSDE installed with the network protocols enabled? With SP3a they are
turned off by default.
Jim
"Neil W." <neilw@.netlib.com> wrote in message
news:eC6V4XdPEHA.1276@.TK2MSFTNGP11.phx.gbl...
> Any idea why the following dmo connection string would not work on MSDE,
but
> does work on SQL Personal Edition? It appears to connects, but then can't
> find the server name. In both cases we are using the latest SQL Personal,
> MSDE, and Windows XP.
> Thanks for any guidance.
> Neil
> connection string:
> server=myserver;trusted_connection="yes";database= northwind;connection
> timeout=10;
> exception:
> [INFO] 20040517 17:59:34.598 (myControl) Connected successfully to
>
server=VPC1W2KSQL\VPC1MSDE;Trusted_Connection=yes; database=master;connection
> timeout=10;
> [EXCEPTION] 20040517 17:59:51.352 (SQLServer) Cannot connect
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or
> access denied.
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).
> at SQLDMO.SQLServerClass.Connect(Object ServerName, Object Login,
Object
> Password)
> at SQLServer.Connect()
>
|||This took me a little bit further. I can now actually see the tables in in
my program using the dmo calls. Thanks! owever, when I try to access the
individual columns, I continue to get errors such as:
EXCEPTION] 20040520 08:37:30.955 (sqlserver.myObject) Error retrieving info
from test2: Server user 'mymachine\mylogin' is not a valid user in database
'test2'.
Again, this all works fine using SQL Personal Edition.
Neil
"Jim Young" <thorium48@.hotmail.com> wrote in message
Was MSDE installed with the network protocols enabled? With SP3a they are
turned off by default.
Jim
"Neil W." <neilw@.netlib.com> wrote in message
news:eC6V4XdPEHA.1276@.TK2MSFTNGP11.phx.gbl...
> Any idea why the following dmo connection string would not work on MSDE,
but
> does work on SQL Personal Edition? It appears to connects, but then can't
> find the server name. In both cases we are using the latest SQL Personal,
> MSDE, and Windows XP.
> Thanks for any guidance.
> Neil
> connection string:
> server=myserver;trusted_connection="yes";database= northwind;connection
> timeout=10;
> exception:
> [INFO] 20040517 17:59:34.598 (myControl) Connected successfully to
>
server=VPC1W2KSQL\VPC1MSDE;Trusted_Connection=yes; database=master;connection
> timeout=10;
> [EXCEPTION] 20040517 17:59:51.352 (SQLServer) Cannot connect
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or
> access denied.
> [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).
> at SQLDMO.SQLServerClass.Connect(Object ServerName, Object Login,
Object
> Password)
> at SQLServer.Connect()
>
|||Carefully read the error message. You need to make the user you are
connecting with, in this case a Windows login, a user in the database you
are trying to use. Everyone that can connect to a server can see the schema
of any database, but you can't select or change any data until you are given
explicit permissions in a database.
Jim
"Neil W." <neilw@.netlib.com> wrote in message
news:OShgsvmPEHA.2468@.TK2MSFTNGP11.phx.gbl...
> This took me a little bit further. I can now actually see the tables in
in
> my program using the dmo calls. Thanks! owever, when I try to access the
> individual columns, I continue to get errors such as:
> EXCEPTION] 20040520 08:37:30.955 (sqlserver.myObject) Error retrieving
info
> from test2: Server user 'mymachine\mylogin' is not a valid user in
database[vbcol=seagreen]
> 'test2'.
> Again, this all works fine using SQL Personal Edition.
> Neil
>
> "Jim Young" <thorium48@.hotmail.com> wrote in message
> Was MSDE installed with the network protocols enabled? With SP3a they are
> turned off by default.
> Jim
> "Neil W." <neilw@.netlib.com> wrote in message
> news:eC6V4XdPEHA.1276@.TK2MSFTNGP11.phx.gbl...
> but
can't[vbcol=seagreen]
Personal,
>
server=VPC1W2KSQL\VPC1MSDE;Trusted_Connection=yes; database=master;connection[vbcol=seagreen]
or
> Object
>
>
Showing posts with label connects. Show all posts
Showing posts with label connects. Show all posts
Friday, March 9, 2012
Friday, February 24, 2012
login security question
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 hav
e to follow.
Thank you in advance.
Sunny
Look 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 TOOL is touching it. If the "connection" from the client application comes in through a username/password, then that username/password has access to SELECT, UPDATE, DELETE, etc fro
m tables.
That is why we do all our database access through STORED PROCEDURES - so actual 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 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 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 changes.
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 username/password, then that username/password has access to SELECT, UPDATE, DELETE, etc f
rom tables.[vbcol=seagreen]
> That is why we do all our database access through STORED PROCEDURES - so actual 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:
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. Could 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 application rolde/password.
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 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 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
>
>
|||<<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 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 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_USER 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...
> Bob,
> Thanks for your reply. I kinda understand this Application Role approach. Could 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 application rolde/password.
> 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 who 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 not 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 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 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_USER 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. 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.
>
>
|||> 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 you say it is not a secure method?
Are you afraid of network sniffing? There's an encryption option in sp_setapprole.
> 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 password (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 completely secure method.
Is there any alternative.[vbcol=seagreen]
> Thanks,
> sunanda.
>
> "Tibor Karaszi" wrote:
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 hav
e to follow.
Thank you in advance.
Sunny
Look 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 TOOL is touching it. If the "connection" from the client application comes in through a username/password, then that username/password has access to SELECT, UPDATE, DELETE, etc fro
m tables.
That is why we do all our database access through STORED PROCEDURES - so actual 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 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 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 changes.
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 username/password, then that username/password has access to SELECT, UPDATE, DELETE, etc f
rom tables.[vbcol=seagreen]
> That is why we do all our database access through STORED PROCEDURES - so actual 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:
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. Could 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 application rolde/password.
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 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 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
>
>
|||<<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 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 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_USER 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...
> Bob,
> Thanks for your reply. I kinda understand this Application Role approach. Could 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 application rolde/password.
> 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 who 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 not 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 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 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_USER 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. 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.
>
>
|||> 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 you say it is not a secure method?
Are you afraid of network sniffing? There's an encryption option in sp_setapprole.
> 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 password (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 completely secure method.
Is there any alternative.[vbcol=seagreen]
> Thanks,
> sunanda.
>
> "Tibor Karaszi" wrote:
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:
>
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:
>
Subscribe to:
Posts (Atom)