ODBC Sql Server Login

[Jim Bunton]

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

 

[David Lloyd]

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.


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