locked
Create USer DSN RRS feed

  • Question

  • Hi.
    Can somebody tell me how to create a system DSN for SQL Server programmatically in vb .net?
    The following code always fails to create the DSN. What am i missing?
      Private Sub Build_SystemDSN_SQL()
            Dim ret%, Driver$, Attributes$
            Driver = "SQL Server" & Chr(0)
            Attributes = "DSN=" & mstrDSNName & Chr(0)
            Attributes = Attributes & "Server=" & mstrServerName & Chr(0)
            Attributes = Attributes & "Database=" & mstrDBName & Chr(0)
            Attributes = Attributes & "Uid=" & mstrUser & Chr(0) & "pwd=" & mstrPwd & Chr(0)
            'Attributes = Attributes & "Trusted_Connection=yes"
            SQLConfigDataSource(0, ODBC_REMOVE_SYS_DSN, Driver, Attributes)
            ret = SQLConfigDataSource(0, ODBC_ADD_SYS_DSN, Driver, Attributes)

            'ret is equal to 1 on success and 0 if there is an error
            If ret <> 1 Then
                MsgBox("DSN Creation Failed")
            End If

        End Sub

    The above code fails to create the DSN if the user name and the password are supplied in the attribute. Otherwise the DSN is created. But i want to save/store the user name and password in the DSN. Any help is greatly appreciated.

    Thanks.

    Saturday, September 8, 2012 10:24 AM

Answers

  • The above code fails to create the DSN if the user name and the password are supplied in the attribute. Otherwise the DSN is created. But i want to save/store the user name and password in the DSN. Any help is greatly appreciated.

    Microsoft SQL Server ODBC drivers do not store the userid and password as part of the DSN configuration for security reasons.  You will need to provide those values programatically when you connect to the database. 


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    • Proposed as answer by Bob Wu-MT Monday, September 10, 2012 7:59 AM
    • Marked as answer by Bob Wu-MT Tuesday, September 25, 2012 2:42 AM
    Saturday, September 8, 2012 1:24 PM
  • Hi Radhakrishnan,

    A similar issue have been solved in this link,

    http://social.msdn.microsoft.com/forums/en-US/adodotnetdataproviders/thread/44234ee8-795f-4214-99af-20f46982e08b/

    Best Regards,


    Bob Wu [MSFT]
    MSDN Community Support | Feedback to us

    • Marked as answer by Bob Wu-MT Tuesday, September 25, 2012 2:42 AM
    Monday, September 10, 2012 7:59 AM

All replies

  • The above code fails to create the DSN if the user name and the password are supplied in the attribute. Otherwise the DSN is created. But i want to save/store the user name and password in the DSN. Any help is greatly appreciated.

    Microsoft SQL Server ODBC drivers do not store the userid and password as part of the DSN configuration for security reasons.  You will need to provide those values programatically when you connect to the database. 


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    • Proposed as answer by Bob Wu-MT Monday, September 10, 2012 7:59 AM
    • Marked as answer by Bob Wu-MT Tuesday, September 25, 2012 2:42 AM
    Saturday, September 8, 2012 1:24 PM
  • Hi Radhakrishnan,

    A similar issue have been solved in this link,

    http://social.msdn.microsoft.com/forums/en-US/adodotnetdataproviders/thread/44234ee8-795f-4214-99af-20f46982e08b/

    Best Regards,


    Bob Wu [MSFT]
    MSDN Community Support | Feedback to us

    • Marked as answer by Bob Wu-MT Tuesday, September 25, 2012 2:42 AM
    Monday, September 10, 2012 7:59 AM