Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

ODBC Sql Server Login

Discussion in 'Information Technology' started by Jim Bunton, Oct 28, 2005.

  1. Jim Bunton

    Jim Bunton
    Expand Collapse
    Guest

    I want
    to present a user with the Login Dialogue for SqlServer Authorisation through an ODBC connection - 'JayRemote' after which I want access to tables linked through the ODBC driver to be without further login.

    ----------------------------------------------------

    Public Sub PromptConnect()
    Dim WkspcODBC As Workspace, NewCon As Connection

    'Create ODBC workspace
    Set WkspcODBC = CreateWorkspace("NewODBCWorkspace", "admin", "", dbUseODBC)
    'Open New Connection to JayRemote
    Set NewCon = WkspcODBC.OpenConnection("Test1", dbDriverPrompt, False, "ODBC;DSN=JayRemote")
    '> This allows login [* see note below] BUT the data source dialogue has to be responded to first Before the Login Dialogue

    Set NewCon = WkspcODBC.OpenConnection("Test1", dbDriverCompleteRequired , False, "ODBC;DSN=JayRemote")
    '> ERROR 3146 ODBC connection failed
    '??? WHY ??? - I think it's attempting Trusted SQL Server Login instead of Server Authorisation

    NOTE:
    If a linked table [linked through Jay Remote] is double clicked the first response is
    'Connection failed' sql state 28000 server error 18452 - login failed, use of null. Not associated with Trusted sql server connection.

    Then the login dialogue is displayed.with Trusted Connection checked.

    After un-checking Trusted Login and logging in then any other linked table is opened without further need for the login dialogue.

    SO - It seems that the code above may well be attempting a 'Trusted Login'. If so how do I persuade it not to do so but to go for Sql Server Authorisation

    HOWEVER * although the code above allows a login STILL the first attempt to open a linked table requires an additional login!

    Jim Bunton
     
  2. Loading...

    Similar Threads Forum Date
    Microsoft Brings 64-Bit Perks in SQL Server 2000 Service Pack (Ziff Davis) Interfaith Dialogues May 7, 2005

  3. David Lloyd

    David Lloyd
    Expand Collapse
    Guest

    Jim:

    Below are two KB articles that deal with linked tables, how to avoid the
    password prompt when viewing linked tables as well as how to re-link the
    tables through code. The second article also shows how to create a DSN
    programmatically. Hopefully, they can give you some insight as to how to
    approach the issues you are having.

    http://support.microsoft.com/default.aspx?scid=kb;en-us;177594
    http://support.microsoft.com/default.aspx?scid=kb;en-us;159691


    --
    David Lloyd
    MCSD .NET
    http://LemingtonConsulting.com

    This response is supplied "as is" without any representations or warranties.


    "Jim Bunton" <jBunton@BlueYonder.co.uk> wrote in message
    news:1Bl8f.4263$iD.504@fe2.news.blueyonder.co.uk...
    I want
    to present a user with the Login Dialogue for SqlServer Authorisation
    through an ODBC connection - 'JayRemote' after which I want access to tables
    linked through the ODBC driver to be without further login.

    ----------------------------------------------------

    Public Sub PromptConnect()
    Dim WkspcODBC As Workspace, NewCon As Connection

    'Create ODBC workspace
    Set WkspcODBC = CreateWorkspace("NewODBCWorkspace", "admin", "",
    dbUseODBC)
    'Open New Connection to JayRemote
    Set NewCon = WkspcODBC.OpenConnection("Test1", dbDriverPrompt, False,
    "ODBC;DSN=JayRemote")
    '> This allows login [* see note below] BUT the data source dialogue has
    to be responded to first Before the Login Dialogue

    Set NewCon = WkspcODBC.OpenConnection("Test1", dbDriverCompleteRequired
    , False, "ODBC;DSN=JayRemote")
    '> ERROR 3146 ODBC connection failed
    '??? WHY ??? - I think it's attempting Trusted SQL Server Login
    instead of Server Authorisation

    NOTE:
    If a linked table [linked through Jay Remote] is double clicked the first
    response is
    'Connection failed' sql state 28000 server error 18452 - login failed, use
    of null. Not associated with Trusted sql server connection.

    Then the login dialogue is displayed.with Trusted Connection checked.

    After un-checking Trusted Login and logging in then any other linked table
    is opened without further need for the login dialogue.

    SO - It seems that the code above may well be attempting a 'Trusted Login'.
    If so how do I persuade it not to do so but to go for Sql Server
    Authorisation

    HOWEVER * although the code above allows a login STILL the first attempt to
    open a linked table requires an additional login!

    Jim Bunton
     

Share This Page