Creating DSN in code - problem

[db999]

Hi
I am trying to create a SQL Server DSN using code. I want to create the DSN for a specified server, database, username and password. I can create a DSN using SQLConfigDataSource without specifying a username and password however when I do specify a username and password the SQLConfigDataSource function fails. I need to create the DSN for a third party product and want to avoid using trusted connections.

Please see the following code example which is based on MSDN article Q171146 HOWTO: Create and Remove a DSN in Visual Basic

Option Explicit

'Constant Declaration
Private Const ODBC_ADD_DSN = 4        ' Add system data source
Private Const vbAPINull As Long = 0&  ' NULL Pointer

'Function Declare
Private Declare Function SQLConfigDataSource Lib "odbccp32.dll" _
(ByVal hwndParent As Long, ByVal fRequest As Long, _
ByVal lpszDriver As String, ByVal lpszAttributes As String) _
As Long 
 
Private Sub Command1_Click()    
Dim intRet As Long
Dim strDriver As String
Dim strAttributes As String
    
    'Set the driver to SQL Server
    strDriver = "SQL Server"
    'Set the attributes delimited by null.
    strAttributes = "SERVER=SERVER1" & Chr$(0)
    strAttributes = strAttributes & "DSN=DSNTest" & Chr$(0)
    strAttributes = strAttributes & "DESCRIPTION=Test DataSource" & Chr$(0)
    strAttributes = strAttributes & "DATABASE=pubs" & Chr$(0)
    
    'Uncommenting the following causes SQLConfigDataSource to fail
    'strAttributes = strAttributes & "uid=sa" & Chr$(0)
    'strAttributes = strAttributes & "pwd=password" & Chr$(0)
    
    intRet = SQLConfigDataSource(vbAPINull, ODBC_ADD_DSN, strDriver, strAttributes)
    If intRet Then
        MsgBox "DSN Created"
    Else
        MsgBox "Create Failed"
    End If

End Sub

I am trying to connect to a SQL Server version 7.0 using VB6 Enterprise and the version of my odbccp32.dll is 3.520.6019.0

Any help would be really appreciated
Thanks
David

 

[timferris]

Hey db999, maybe you should consider going around every single users computer and putting the dsn on manually....................just a thought.

All the best

 

[MDA]

Hey db999,

Did you ever figure this one out? I am having the same problem... I need to connect with SQL authentication using sa but for some reason I get an error when I use UID and PWD. I also tried using lastuser but did not seem to work. I noticed that the registry entry just has LastUser and not password, can't figure it out.

Let me know if you figured this one out..

Thanks,

MDA

 

[johnwm]

MDA;
Have you tried a search on this forum for DSN? This question has been answered many times! Let me know if this helps
________________________________________________________________
If you are worried about how to post, please check out FAQ222-2244 first

'There are 10 kinds of people in the world: those who understand binary, and those who don't.'

 

[db999]

MDA,
Sorry I never did get the code to work. I ended up using trusted connections.
When running the SQLConfigDataSource() function you can use the SQLInstallerError() function to get the reason why the SQLConfigDataSource() failed.

See following link for details

http://builder.com.com/article.jhtml?id=u00820010816adm02.htm&fromtm=e065

In my case the error message was 'Invalid keyword-value pairs'. Looking into this a bit further I found an article on
the Microsoft website specifing the allowed keyword-value pairs for SQL Server. This can be found at

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbcsql/od_odbc_c_99yd.asp

There is no mention of including the username and password as keyword-value pairs so I assume this is not possible.

johnwm, I searched the site and cannot find anthing that helps with this particular error. Any code that I use returns the same error. Perhaps you could point us at a specific posting.
Thanks.
db999