Re: sql connection security

• Previous message: Ulf Brügelmann: "Re: Openrowset Jet OLEDB"
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]

Date: Wed, 30 Apr 2003 17:13:49 -0400

Karthik,

Sorry, but I do not understand what you are asking ... but taking a guess:

> If we have the concept of application roles, why should i hard code the user
> name and password ?
With application roles, the application has to supply the application login and password ... thus the application has to know the application login and password, which usually means that this is hard coded information.

> The user name that the user uses to login to my system will become the login
> credentials for SQL Server, would it not ?
> That is if i login to my windows app as "abc" / "abc", then my sql server
> user name would be "abc" / "abc", would it not ?
Not if you use an application role.

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.

"karthik" <karthik@maximizelearning.com> wrote in message news:%237Dft7tDDHA.3252@TK2MSFTNGP11.phx.gbl...
> Hi,
>
> Thanks a lot on telling me about application roles. I do have some
> clarifications though.
>
> If we have the concept of application roles, why should i hard code the user
> name and password ?
>
> The user name that the user uses to login to my system will become the login
> credentials for SQL Server, would it not ?
>
> That is if i login to my windows app as "abc" / "abc", then my sql server
> user name would be "abc" / "abc", would it not ?
>
> Regards,
> Karthik.
>
> "BP Margolin" <bpmargo@attglobal.net> wrote in message
> news:e84PsitDDHA.2572@TK2MSFTNGP11.phx.gbl...
> Karthik,
>
> In general, if you provide a user with a login and password then that user
> is going to be able to connect to SQL Server using that login and password
> from any application ... Query Analyzer, Enterprise Manager, MS Access, etc.
>
> However, you might check out the documentation on "application roles" in the
> SQL Server Books Online. "Application roles" sounds like it is what you
> want, except for the fact that it still is going to require a hard-coding of
> the login and password somewhere ... in your code, in the registry, or
> somewhere else ... so I don't know that it really helps you achieve your
> apparent goal.
>
> But bottom line, with the exception of "application roles", there is NO way
> that you can assign a user a login and password, and then restrict that user
> only to your application.
>
> Well perhaps there is one, not necessarily fail-safe, alternative. Assuming
> that you have coded your application using stored procedures, you can grant
> EXEC permissions on the stored procedures, and DENY permissions on
> everything else (tables, views, etc.). You can then make sure that your
> application, when connecting to SQL Server sets the "Application Name"
> parameter in the connection string, and then in each and every stored
> procedure, use the APP_NAME ( ) function to verify that the connection has
> the correct application name. The "catch" here is that **any** application
> can set the "Application Name" parameter to anything it wants, thus
> providing an end-run around this particular approach.
>
> -------------------------------------------
> BP Margolin
> Please reply only to the newsgroups.
> When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
> can be cut and pasted into Query Analyzer is appreciated.
>
> "karthik" <karthik@maximizelearning.com> wrote in message
> news:eTvNoTtDDHA.3072@TK2MSFTNGP11.phx.gbl...
> > Hi,
> >
> > I have a windows application that has a list of users and their passwords.
> > All this information is stored in a SQL 2000 database. My application at
> > present connects to SQL server via a fixed user name and password say
> > test/test.
> >
> > This results in me hardcoding the value somewhere in the code. What i
> > thought i could do is, create similar SQL users as the one that is there
> in
> > the database. Like lets say i have a user called "abc" with password "abc"
> > in my user database, i will create a similar sql user.
> >
> > That way my connection to the application will not have hardcoded user
> names
> > and passwords.
> >
> > But what i need to make sure now is that a user should not get access to
> sql
> > server directly... i mean just by connecting from one enterprise manager
> to
> > my server's enterprise manager. How can i do this ?
> >
> > In short, what i need is that i have to make sure that users in SQL server
> > should not be able to connect directly to SQL. They should only connect
> via
> > my windows application. How can i achieve this ?
> >
> > And if someone can throw more light into the way i authenticate users it
> > would be of great help!
> >
> > Thanks a ton!
> >
> > Regards,
> > Karthik.
> >
> >
>
>

• Previous message: Ulf Brügelmann: "Re: Openrowset Jet OLEDB"
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]