Re: How does ODBC's authentication work?

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Steve Thompson (stevethompson_at_nomail.please)
Date: 09/17/04

  • Next message: Brannon Jones: "Re: detecting a dead connection" 
    Date: Fri, 17 Sep 2004 14:54:37 -0400

    "serge" <sergea@nospam.ehmail.com> wrote in message
    news:rVf2d.20068$0h7.1418798@news20.bellglobal.com...
    > I am trying to understand this. When i choose either windows NT
    > authentication,
    > or SQL authentication and specify an SQL login and a password, the user
    > credential
    > is only being used ONCE?

    It's used once during the connection.

    > That means, let's say i used the SQL Login "sa" and its password to point
    to
    > a SQL Server 2000 db, after i have finished creating the ODBC (System
    DSN),
    > can i disable the sa account and the ODBC will still be valid and
    > operational for the
    > application that is using it?

    No, security does not work that way... the account you specify to connect is
    used each time you connect to SQL Server, then appropriate permissions
    determined on a database basis. BTW, you can not disable the 'sa' account,
    you could consider using Windows Authentication instead.

    You may also want to investigate application roles as another possibility if
    you want to embed the account information in the application.

    > In other words, whatever user i use during the creation of the ODBC, i
    don't
    > or the ODBC will never use that user again?

    As before, that will not work...

    > If that's the case, then i can always use the least powerful sql login for
    > example, as
    > long as that login has access to the SQL db i am pointing to, correct?

    Minimum rights on access is always a good idea.

    > I mean, it's
    > not necessary to use the "sa" login, since some people do not want to use
    > the "sa"
    > account for anything, they simply disable it and stop using it.
    >
    > The password that i specify for the SQL login is only used there once also
    > right?
    > It does not get saved anywhere on the hard drive, nor in the registry?

    Consider using Windows Authentication, far more secure...

    Steve

  • Next message: Brannon Jones: "Re: detecting a dead connection"

    Relevant Pages

    • Re: Distributed Query - Linked Servers?
      ... In the case of SQL style accounts, it doesn't seem very secure to me, because the password will be transmitted over the network. ... it should also be able to work by setting up a credential and proxy account on Server1. ... In this case it could work with NTLM authentication. ... we just need to verify that the 2 sql server instances are under sql server and windows authentication mode and the sql account you use has the permission on the 2 sql server instances. ...
      (microsoft.public.sqlserver.server)
    • RE: Integrated Authentication (Kerberos) Problem
      ... Verify the SPN for the SQL service account is registered such as the ... >Thread-Topic: Integrated Authentication Problem ... A Windows XP SP1 with IE6 client machine ...
      (microsoft.public.inetserver.iis.security)
    • Re: Utter madness!
      ... If it's going to be "tricky" to get a trusted connection to my SQL box ... certain authentication scenarios are harder in that set ... To do the service account approach, ...
      (microsoft.public.dotnet.framework.aspnet.security)
    • Re: SQL Adapter Setup
      ... My VPC's computer account had gone AWOL from ... VS project, select the SQL Adapter, enter the db ... security, test the connection, specify namespace, root ... element names, select the stored procedure, specify the valuepassed, ...
      (microsoft.public.biztalk.general)
    • Re: Utter madness!
      ... Lots of people run SQL on other boxes. ... certain authentication scenarios are harder in that set up. ... Another reason is that you can avoid the whole Kerberos delegation ... To do the service account approach, ...
      (microsoft.public.dotnet.framework.aspnet.security)