Re: sql connection security

From: karthik (karthik_at_maximizelearning.com)
Date: 04/30/03 

Date: Wed, 30 Apr 2003 11:39:09 +0530

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.
>
>

Relevant Pages

  • Login Seems OK but doesnt behave correctly
    ... Using MS SQL server on a shared host (www.joglab.com shows the ... login but not behaving on the pages ie LoginView/ Login Status not ... Web Site Administration Tool I get "Could not establish a connection ... <LoggedInTemplate> ...
    (comp.databases.ms-sqlserver)
  • Re: forms authentction against sql server 2000
    ... Try using Query Analyzer using the same login and see. ... I'm using sql server 2000 on a remote machine and I have a login.aspx page ... "An error has occurred while establishing a connection to the server. ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: VB.NET Mailmerge using Stored Procedures
    ... connect to some stored procedures and functions: when SQL Server executes a ... an empty .odc file and put all the connection information ... connect to a SQL Server 2000 procedure called myprocedure with two ... the only other way to make stored procedures work is to ...
    (microsoft.public.word.mailmerge.fields)
  • Re: Login failure...
    ... loop) -- I'm suspecting the connection overhead with rapid ... connection/disconnect timing problem (i.e. my remote SQL Server can't keep ... > Private Sub MyMethod ... and I get Login failure. ...
    (microsoft.public.dotnet.framework.adonet)
  • Whats the best way to open a Connection to DB in the WEB envoironment
    ... applied an ideal logic to open a connection from my application. ... Backend Database: SQL Server 2000. ... I have created "Login" for each users in SQL Server 2000 and all of them ...
    (microsoft.public.dotnet.framework.adonet)
Loading