Results 1 to 12 of 12
  1. #1
    Join Date
    Aug 2012
    Posts
    12

    Unanswered: ODBC to SQL server

    I am trying to setup an ODBC data source for Access. The server I'm trying to connect to is 2008 R2. I have SQL Server Management Studio installed on the computer where I'm trying to make the ODBC Data Source from. I can connect the the remote 2008 server through Management Studio, but in the ODBC wizard I do the following:

    "Machine Data Source" tab, click "New"
    Select "User Data Source", click "Next >"
    Select "SQL Server", click "Next >"
    Click "Finish"
    Name: remote
    Description: remote
    Server: type the server name ie "sql.somewhere.com"
    Select "With SQL Server authentication using a login ID and password entered by the user."
    Login ID: sa
    Password: my password
    Click "Next >"

    I get the following error:
    Connection failed:
    SQLState: '01000'
    SQL Server Error: 14
    [Microsoft][ODBC SQL Server Driver][DBNETLIB][ConnectionOpen
    (Invalid Instance()).
    Connection failed:
    SQLState: '08001'
    SQL Server Error: 14
    [Microsoft][ODBC SQL Server Driver][DBNETLIB]Invalid connection.

    I have looked all over the net trying to figure this out and I'm just lost. Any help is greatly appreciated!

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    You may want to use a System ODBC, rather than a User ODBC. The difference is that the User ODBC is tied to one user account on the machine.

    For the server name in the ODBC settings, are you typing in the exact same server name as in SQL Management Studio?

    Is there a firewall on this machine? If so, is there an exception in the rules (inbound) for the SQL Server Management Studio program? If so, then it would be able to pass through the firewall, but ODBC would not.

  3. #3
    Join Date
    Aug 2012
    Posts
    12
    The server name is the same. There is a firewall on this machine and there is a rule for Management Studio, so I need to setup a Firewall exception for ODBC?

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Odds are good that the servername is your problem. Very few (if any) DNS servers will properly resolve a name like that without manual adjustments.

    MCrowley has a good point. A System connection is more useful and easier to debug

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Aug 2012
    Posts
    12
    I've changed to a System connection and am trying the IP of the server. I get the same errors. Are ODBCs meant for over the internet or connecting on a local network?

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    You can open up the browser port (UDP 1434) to allow access for the server, and possibly the TCP port, if the remote SQL Server is not running on port 1433 (default).
    Code:
    netsh advfirewall firewall add rule name="SQL Browser" dir=in protocol=udp remoteport=1434 action=allow
    This will need to run from a command line with admin rights. You trust random code from the internet enough to let it run with admin rights, right? ;-)

  7. #7
    Join Date
    Aug 2012
    Posts
    12
    Okay I made some progress. I created a System DSN, it turns out I have to unselect Dynamic Port and make sure it said 1433. I can go in to the ODBC Data Source Administrator, click my DSN and test it. It says it worked successfully. Now in Access I click "External Data", "ODBC Database", select "Link to blah blah blah...", click "Machine Data Source" and select my DSN and click "OK".

    It prompts me for my password, so I type it in and I get the same error as before. Why should I be getting any error at all if the DSN tested correctly?

    Thanks!

  8. #8
    Join Date
    Aug 2012
    Posts
    12
    I also tried creating a DSN file, I can Add it via External Data/ODBC Database, it asks for my password and then I can link my tables. I saved the access file, closed access and reopened the file. With this scenario I get the following error:

    Connection failed:
    SQLState: '28000'
    SQL Server Error: 18452
    [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.

    The remote server is on a domain, the local computer is not, if that helps.

    Any help is appreciated!

  9. #9
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    SQLState 28000 is usually a bad password or other login problem. Can you make sure that you are using SQL Authentication (not Windows Authentication) in the DSN definition?

    Also, make sure that Access is not defaulting to Windows Authentication, as that will override the ODBC definition.

  10. #10
    Join Date
    Aug 2012
    Posts
    12
    How can I verify if Access is defaulting to Windows Authentication?

    Thanks!

  11. #11
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Danged if I know. I haven't touched Access in about 13 years, now.

  12. #12
    Join Date
    Aug 2012
    Posts
    12
    Haha, well thanks for the tip anyway!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •