none
ODBC connection to SQL 2005 Express

    Question

  • I am attempting to setup a SQL Server 2005 Express database as a backend to an Access MDB, and need to establish an ODBC data source for the backend database.  On two different systems (Windows XP Pro and Windows 2003 SBS), I get an error that says I cannot connect to the SQL Server.  I get a message that says:

    Connection failed:  SQL State 08001  SQL Server Error: 2
    [Microsoft][SQL Native Client]Named Pipes Provider:  Could not open a connection to SQL Server

    That is followed by a Login timeout expired and a suggestion that under the default settings SQL Server does not allow remote connections.

    I have shared memory, named pipes and TCP/IP protocols enabled, and the firewall set to allow connections on port 1433.  And the Express manager seems to work OK.

    I see several posts about issues with ODBC, but none of the suggestions seem to help in this situation.  Can anyone suggest something to try?  Thanks in advance.

    Wednesday, January 18, 2006 10:26 PM

Answers

  • I had already seen that link and tried it with no success.  I did however finally solve the problem.  It has to do with instances, and the names they are given.

    It turns out you must specify the instance after the server name, so in the server name in the dialog box for creating the ODBC Data Source, you must enter it as either:
    MyServer\SQLEXPRESS or
    .\SQLEXPRESS

    Once I changed the server name, the connection came up cleanly.

    Thursday, January 19, 2006 12:52 PM

All replies

  • I had already seen that link and tried it with no success.  I did however finally solve the problem.  It has to do with instances, and the names they are given.

    It turns out you must specify the instance after the server name, so in the server name in the dialog box for creating the ODBC Data Source, you must enter it as either:
    MyServer\SQLEXPRESS or
    .\SQLEXPRESS

    Once I changed the server name, the connection came up cleanly.

    Thursday, January 19, 2006 12:52 PM
  • Thanks Wendell for posting the solution - I was having the same problem.  Your solution resolves it for me also.
    Monday, January 30, 2006 4:59 AM
  • Great! It's really very helpful to me!
    Friday, February 10, 2006 4:48 PM
  • trying to connect to sqlexpress on remote computer using .udt file, refuses to connect until I create a system DSN with a named pipe connection. TcpIp won't do, it has to be a named pipe. Udt then works fine with integrated security. Delete DSN and the Udt still works. No changes made at server end. Any comments as to why a DSN has to be created then can be removed.
    Client is win2k server is xpPro
    Monday, February 13, 2006 7:50 PM
  • Most likely after using DSN, a successful connection string is cached in registry.

    The reason that TCP don't work when server is XP sp2 is that the TCP port is blocked by windows firewall. You can make exception in the firewall to your sqlservr.exe service or the specific TCP port that sqlservr.exe is listening on.

    Thanks,

    Nan

    Thursday, February 16, 2006 8:38 AM
  • too funny.. was trying to figure the same thing out for days

    i had  SERVER/SQLEXPRESS

    it had to be:  SERVER\SQLEXPRESS

    i had the wrong frickin' slash.. grrr!!!

    Friday, March 17, 2006 4:17 AM
  • First, the error tells that you are making named pipe connection instead of TCP connection.Secondly, The error number [2] indicates that either your sql server was not started successfully or you connect to a pipe that server is not listeing on or your remote connection was blocked somehow by firewall.

    To troubleshoot:

    1) Use "net start" to see whether your sql server was started successfully, and listening on named pipe. go to server error log, there should have "Server named pipe provider is ready to accept connection on  <PipeName>".

    2) By default, SQL Server Express is installed as a named instance, hence, you should see the pipe name looks like \\.\pipe\mssql$SQLExpress\sql\query.

    3) Check your connection string, you should specify "Server=<MachineName>\SQLExpress ....", also double check sqlbrowser was running.( "sc query sqlbrowser" )

    4) If all of above doesn't work, you can try " telnet <ServerIP> 445" to see whether your "File and Print Sharing" was disabled by firewall.

    5) If you can not make named pipe connection by all means, try TCP(by specifying "tcp:" prefix in your connection string ) see whether it works.

    Monday, March 20, 2006 6:25 PM
  •  WendellB wrote:

    I had already seen that link and tried it with no success. I did however finally solve the problem. It has to do with instances, and the names they are given.

    It turns out you must specify the instance after the server name, so in the server name in the dialog box for creating the ODBC Data Source, you must enter it as either:
    MyServer\SQLEXPRESS or
    .\SQLEXPRESS

    Once I changed the server name, the connection came up cleanly.



    I had this problem and directly I used
    .\SQLEXPRESS
    it then worked thanks, it only cost me 5 hours until i found this post! :)
    Sunday, November 26, 2006 11:47 PM
  • I had the same problem and your solution worked first time. Thanks for the help
    Monday, December 25, 2006 7:52 AM
  • thanks a lot!!

    was trying to figure out for hours.. and i found this post and it just worked!!

    =)

     

    Sunday, February 25, 2007 5:16 PM
  • Server side:

     

    1. What is the MS SQL version? [ SQL Server 2005 | SQL Sever 2005 ]
    2. What is the SKU of MS SQL? [Enterprise].
    3. What is the SQL Server Protocol enabled? [TCPIP ].
    4. Does the server start successfully? [YES |
    5. If SQL Server is a named instance, is the SQL browser enabled? [YES]
    6. What is the account that the SQL Server is running under?[Local System ]
    7. Do you make firewall exception for your SQL server TCP port if you want connect remotely through TCP provider? [YES]
    8. OS- Windows 2003 server

    When I am trying to import data from AS400, I do not find iseries access for ODBC driver, whereas it is available in SQL server 2000. When I try using IBM DB2 UDB for iseries IBMDA400 OLEDB provider, & trying to to test the connection, i get the following MICROSOFT DATA LINK ERROR

     

    " Test connection failed because of an error initializing provider. CWBCO1011 - Remort port could not be resolved"

     

    Is this problem related to MS SQL server 2005 or Windows 2003 server or IBM iseries driver?

     

    I serached the net & I was not able to find a proper solution. I would appreciate if anybody could throw some light on this or where i could get any information related to this.

     

    Thanks in Advance!

     

    Rajesh

    Wednesday, March 28, 2007 12:02 PM
  • Thanks everyone for your efforts and for relating your experiences and frustrations. By piecing the tidbits together I was finally able to get an ODBC connection made to my SQL Express database using .\SASMB_VRA and Named Pipes. I still don't have an Import/Export option on the context menu for a given database but at least I'm getting closer to being able to export data to Access 2007 so that I can more easily manipulate it (I know virturally nothing about SQL and little about databases).
    Tuesday, April 03, 2007 7:52 PM
  •  

    Hi people i am getting a problem when im creating a dsn to sonnect the SQL server as the database for my content server.....................i tried all the tricks given by you people but its still not workin it is showins a message that .............

     

    Connection failed.

    SQL State'01000'

    [Microsoft][ ODBC SQL Server Driver][Named Pipes]Connection Open

    (Connect()).

    Connection Failed:

    SQL State:'08001'

    SQL Server Error:17

    [Microsoft ][ODBC SQL Server Driver ][Named Pipes]SQL Server doe not exists or access denied............................

     

    Please any body can help me out with this problem..............?

    Monday, April 23, 2007 5:21 AM
  • Thanks for this... This also solved my problem...
    Monday, June 11, 2007 2:12 PM
  • Chetan Singh:

    You have to use the above mentioned solution with named pipes instead of TCP/IP.

    i.e. have the "Named Pipes" radio button checked in the advanced window and make your server name  'ServerName\InstanceName'

    Hope that helps.
    Wednesday, October 31, 2007 5:01 PM
  • Thanks for your post.  It did the trick.
    Tuesday, October 28, 2008 9:20 PM
  • Yeah. I followed all these steps and I still haven't resolved this issue. Now I'm doing this on an Windows XP sp 3 machine. But none of these steps are helping me at all.

    Is there anything else that needs to be checked out. I need this setup or my customer can't do his day-to-day work.

    I don't get it.

    I tried the "servername\ instance name". It's not connecting folks.
    Tuesday, February 23, 2010 2:56 PM
  • I have the same problem(SQL Server Error:17).

    I'm using windows XP ODBC wizard at the client and SQL 2005 Express on the server.

    I know about "server\sqlexpress" and I know about SQL able to accept remote connections TCP/IP and Names Pipes etc. The problem is: Never something like that happened at the client with me and I only know to use ODBC wizard to install than.

    Before I suggest to my client to reinstall windows and so on, coud anybody help me??? I'm very glad to read answers.

    Ivo
    São Paulo - SP - Brazil

    Thursday, March 11, 2010 5:24 PM
  • It's very useful to me! Thank you very much!

    Friday, April 16, 2010 3:32 PM
  • need help on this error.....<!-- [if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:TrackMoves/> <w:TrackFormatting/> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:DoNotPromoteQF/> <w:LidThemeOther>EN-US</w:LidThemeOther> <w:LidThemeAsian>X-NONE</w:LidThemeAsian> <w:LidThemeComplexScript>X-NONE</w:LidThemeComplexScript> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> <w:SplitPgBreakAndParaMark/> <w:DontVertAlignCellWithSp/> <w:DontBreakConstrainedForcedTables/> <w:DontVertAlignInTxbx/> <w:Word11KerningPairs/> <w:CachedColBalance/> </w:Compatibility> <m:mathPr> <m:mathFont m:val="Cambria Math"/> <m:brkBin m:val="before"/> <m:brkBinSub m:val="--"/> <m:smallFrac m:val="off"/> <m:dispDef/> <m:lMargin m:val="0"/> <m:rMargin m:val="0"/> <m:defJc m:val="centerGroup"/> <m:wrapIndent m:val="1440"/> <m:intLim m:val="subSup"/> <m:naryLim m:val="undOvr"/> </m:mathPr></w:WordDocument> </xml><![endif]--> <!-- /* Font Definitions */ @font-face {font-family:"Cambria Math"; panose-1:2 4 5 3 5 4 6 3 2 4; mso-font-charset:1; mso-generic-font-family:roman; mso-font-format:other; mso-font-pitch:variable; mso-font-signature:0 0 0 0 0 0;} @font-face {font-family:Calibri; panose-1:2 15 5 2 2 2 4 3 2 4; mso-font-charset:0; mso-generic-font-family:swiss; mso-font-pitch:variable; mso-font-signature:-1610611985 1073750139 0 0 159 0;} /* Style Definitions */ p.MsoNormal, li.MsoNormal, div.MsoNormal {mso-style-unhide:no; mso-style-qformat:yes; mso-style-parent:""; margin-top:0in; margin-right:0in; margin-bottom:10.0pt; margin-left:0in; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} .MsoChpDefault {mso-style-type:export-only; mso-default-props:yes; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:Calibri; mso-fareast-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi;} .MsoPapDefault {mso-style-type:export-only; margin-bottom:10.0pt; line-height:115%;} @page Section1 {size:8.5in 11.0in; margin:1.0in 1.0in 1.0in 1.0in; mso-header-margin:.5in; mso-footer-margin:.5in; mso-paper-source:0;} div.Section1 {page:Section1;} -->

    2010/04/12 16:23:0.000          1          1          (0) ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'Index'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax. SQLSTate: 37000 ('Line 13 of script 'FINISH.Do, line 394'')

    anybody knows about this error please help!!!!!!!!!!!!! 

    Tuesday, April 20, 2010 6:23 AM
  • I was struggling with a connection error for hours attempting to follow an install guide with no luck until I used the sql instance name in the server field as you indicated. Thanks WendellB.
    Friday, February 25, 2011 7:35 PM
  • Thanks for your posting.  I was helpful!
    Friday, May 06, 2011 8:14 PM
  • This forum was a great help. ^_^

    ^_^

    Friday, November 16, 2012 5:12 AM