How do you specify a DSN name for an ODBC.OdbcConnection object?

Posted on 2005-05-04
Last Modified: 2012-08-14
I am trying to open a connection to an MSSQL database using a DSN with the ODBC.OdbcConnection object and am using the following string:
Public strCnn = (""PROVIDER=MSDASQL;DSN=MyDSNName;"")

Here is the Code to attempt the data call:
Dim cnn As OdbcConnection = New Odbc.OdbcConnection(strCnn)
Dim cmd As OdbcCommand = New OdbcCommand("{CALL spCustAuthentication(?,?,?)}", cnn)
Dim retVal As String

cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@EmailUserName", OdbcType.VarChar, 50).Value = mstrEmailUserName
cmd.Parameters.Add("@UserPssword", OdbcType.VarChar, 50).Value = mstrUserPssword
cmd.Parameters.Add("@UserID", OdbcType.VarChar, 100).Direction = ParameterDirection.Output

cmd.Connection.Open()  'Failure happens at this point with

This is the error I get:
ERROR [08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied. ERROR [01000] [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()). ERROR [01S00] [Microsoft][ODBC SQL Server Driver]Invalid connection string attribute

Any suggestions?
Question by:dhquest
    LVL 29

    Expert Comment

    >> access denied.

    Where is your user id and password?  (Not that I would put them in code nor post them here, but I would indicate something so it was obvious to the experts I didn't forget.

    "PROVIDER=MSDASQL;DSN=MyDSNName;DATABASE=pubs;UID=<username>;PWD=<strong password>;"

    LVL 12

    Expert Comment

    by:fruhj has a huge inventory of connection strings for all occasions and systems.

    If you're using a hosted server that you don't have direct access to - then the DSN route might not be possible for you.

    Please note that it's possible to setup a "DSN-Less" connection string either in code or in your web.config file and use it in your code - in that scenario you wouldn't need the DSN at all.

    that might be better or worse - it all depends on how you feel about setting up a DSN on the server.

    - Jack

    Author Comment


    "UID=<username>;PWD=<strong password>;"
    I was not sure if that was necessary, since the DSN is already configured with that information.  Is it necessary?  If so, do I not specify the UID and PW in the DSN?

    Author Comment

    I also went to (very helpful by the way, thanks) and copied the example:


    No Luck, still says that:
    System.Web.HttpUnhandledException: Exception of type System.Web.HttpUnhandledException was thrown. ---> System.Data.Odbc.OdbcException: ERROR [08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.
    ERROR [01000] [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).
       at System.Data.Odbc.OdbcConnection.Open()

    But when I set up the DSN and test it, no problems...
    LVL 12

    Expert Comment

    can you use one of the other connection strings at I would try one of the SQL ones at the top of the page.

    and I assume you replaced the <my...> values with the real thing right?

    Make sure you set it up as a system DSN, and not a user DSN - the user DSN will only work for the user you set it up as, not for the I_User account that runs most anonymous IIS stuff.

    Author Comment

    I will try one of the other connection strings.  Do I need to change the object type from Odbc?  I have experimented with some of the strings, but I will try all.

    Just a note, I am not even able to get the localhost web to connect to the server (all on the same network).  Would there be something else that I should check?

    As for the replacing of the values, yes.

    Yes, it is a system DSN, not user.
    LVL 12

    Expert Comment

    It looks like you're using .net for development, and I assume your using SQL server 2000 right (you said MSSQL, not MYSQL right?)

    I'll also assume you're not using visual studio? (if you are, there's a few drag and drop operations that make this easy)

    For that I would use the sql specific elements
    You'll need to import
    you can then use the sqlconnection (.net) examples on
    (actually they have sample code for declaring the connection too - have a look at that - only a few lines)


    Author Comment

    Yes, .Net.
    Yes, SQL Server 2000.
    Yes, VS.Net (although I am brand new to it, background is in VB6)
    Maybe you could shed some light on the easy drag and drop stuff?
    LVL 12

    Accepted Solution

    on the left side, you probably have a tab for "server explorer" (if not it's under the view menu)
    click the little plus computer icon - this will help you create a connection to the SQL server.
    now below you'll have that server listed under servers

    you can expand that until you see tables
    for now, just drag a table down to your aspx page (stored procedures are alittle more complex so lets get the table workign first)

    A sql connection and sqlDataadapter object will be created automagically

    now right click the sqldataadapter1 and choose "generate dataset" (choose new)

    now lets link to a table so we can see if it the toolbox
    tab on the left side (or from the view menu)

    choose the webforms toolbox list
    drag a datagrid to the aspx page
    bring up properties for the DataGrid
    Set datasource using the drop down list

    now you're almost there
    you need to add 2 lines of code still

    (adjust to the names of your objects)

    now hit play see the table

    (I'll have to look up a Stored procedure example tonight- cant do that one off the top of my head)

    hit play


    Author Comment


    System.Web.HttpUnhandledException: Exception of type System.Web.HttpUnhandledException was thrown. ---> System.Data.SqlClient.SqlException: SQL Server does not exist or access denied.

    I have access to the server, and I double checked all the security rights.
    LVL 12

    Expert Comment

    is this a build error or a run time error?

    Are you able to drill down using the server explorer and see the list of tables in your database?

    LVL 12

    Expert Comment

    can you try a simple select * from table query using the microsoft query analyzer (QA has you enter your credentials - just one more validation)

    Author Comment

    Run time error, I think.
    Yes, I can drill down and see the tables, views, and stored procs
    Yes, I do a simple select and even run an SP in QA under the user configured to access the data via the DSN connection
    LVL 12

    Expert Comment

    that seems wierd.

    drill down into the generated code
    look for a line like this:
    // sqlConnection1
    this.sqlConnection1.ConnectionString = "workstation id=MYPC;packet size=4096;integrated security=SSPI;data source=MYPC;" +
    "persist security info=True;initial catalog=Northwind";

    this is what was generated when I did up the example I gave you.
    the default is to use integrated security - which doesn't require your password to be entered - I think it uses the aspnet account or the current user account (can't remember which)

    anyhow, you might copy and paste that line, comment one out, and modify the other using some examples from and see if you have better luck


    Author Comment

    Following is the string it created

    "workstation id=""PGRM-1"";packet size=4096;user id=MyUserName;data source=MyServerName;persist security info=False"

    Does this look right?
    LVL 22

    Expert Comment

    No comment has been added to this question in more than 21 days, so it is now classified as abandoned.  I will make a recommendation to the moderators on its resolution in a week or two.  I appreciate any comments that would help me to make a recommendation.

    Unless it is clear to me that the question has been answered I will recommend delete without refund of points.  It is possible that a Grade less than A will be given if no expert makes a case for an A grade.  It is assumed that any participant not responding to this request is no longer interested in its final disposition.

    If the asker does not know how to close the question, the options are here:

    EE Cleanup Volunteer

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
    I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    25 Experts available now in Live!

    Get 1:1 Help Now