none
[IM002][Microsoft][ODBC Driver Manager] Data source name not found x64

    Dotaz

  • HELP!

    We receieve an error when our (newly ported to x64 but's worked for years as x86) DCOM service tries to connect to SQL 2008 R2 using ODBC.  I've searched for similar issues but nothing really matches our problem.

    My SQL Server is SQL 2008 R2 (SQLEXPRESS) running on local machine.  Server and both 32bit and 64bit clients are configured to use TCP and Shared Memory.

    My ODBC DSN name is (for example) FRED.   I've created FRED as a System DSN using both the x86 (SysWOW64) and the x64 version of odbcad32.exe and SQL Native Client 10.0  (2009.100.1600.01).   Both DSN's test successfully.  

    Our application is a x64 DCOM service running under the Local SYSTEM acocunt on the same machine as SQL Server.  (Error also occurs when run as .\Administrator)

    Interactive ODBC test tools correctly create a connection when run as the interactive user.

    So at this point we believe the connection failure has to do with permissions, but nothing's showing up in the event log and I'm out of ideas.


    This signature unintentionally left blank.

    5. března 2012 19:58

Odpovědi

  • Just resolved the problem.  It turns out there as a slight behavioral difference in SQLDriverConnect when called from a x64|UNICODE build vs a Win32|MCBS build. 

    For the Win32|MCBS version, it was safe to pass the same buffer for both the  InConnectionString and the OutConnectionString. I.E.

    SQLTCHAR dsn[1024] = _T("DSN=MYDB;UID=FOO;PWD=BAR"); SQLRETURN n; SQLSMALLINT len; n = SQLDriverConnect(hdbc, NULL, dsn, SQL_NTS, // in dsn, 1024, // out &len, SQL_DRIVER_NOPROMPT);

    In Win32 and MCBS, the above code (right or wrong) worked ok for the past 16 odd years.  The connection was made, *then* the buffer was filled with the completed string.  In x64 and UNICODE however it fails.  It appears that the buffer is being cleared prior to attempting the connection.  Making a copy of the string similar to the example below got the code working again, and seems to prove the theory.

    SQLTCHAR dsn[1024] = _T("DSN=MYDB;UID=FOO;PWD=BAR");
    SQLRETURN n;
    SQLSMALLINT len;
    SQLTCHAR* copy;
    
    copy = (SQLTCHAR*)_tcsdup((PCTSTR)dsn);
    n = SQLDriverConnect(hdbc, 
                         NULL, 
                         copy, SQL_NTS, // in
                         dsn, 1024,    // out
                         &len,
                         SQL_DRIVER_NOPROMPT);
    free(copy);


    This signature unintentionally left blank.

    • Označen jako odpověď Nick F. _ 6. března 2012 17:10
    6. března 2012 17:09

Všechny reakce

  • Hi Nick F. _,

    Based on your description, the application connects to SQL Server is 64-bit. In this case, 64-bit SQL Server Native Client is required. Meanwhile, you should make use of 64-bit ODBC Data Source Administrator tool to create the DSN.

    I would like to recommend you delete all the DSN defined in both 32-bit and 64-bit ODBC Data Source Administrator tools, create a new DSN on 64-bit ODBC Data Source Administrator tool, and try again. In case you are not installed 64-bit SQL Server Native Client, you can obtain from here.

    TechNet Subscriber Support
    If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.


    Stephanie Lv

    TechNet Community Support

    6. března 2012 4:47
  • Stephanie, I appreciate any help I can get.

    As I indicated in my first message, we don't have any problem connecting from either 32bit or 64bit in the context of a desktop application.  However it fails when we try from our service.

    I downloaded the client as you suggested, ran the install and selected the repair option.  This did not help.


    This signature unintentionally left blank.

    6. března 2012 11:25
  • Just resolved the problem.  It turns out there as a slight behavioral difference in SQLDriverConnect when called from a x64|UNICODE build vs a Win32|MCBS build. 

    For the Win32|MCBS version, it was safe to pass the same buffer for both the  InConnectionString and the OutConnectionString. I.E.

    SQLTCHAR dsn[1024] = _T("DSN=MYDB;UID=FOO;PWD=BAR"); SQLRETURN n; SQLSMALLINT len; n = SQLDriverConnect(hdbc, NULL, dsn, SQL_NTS, // in dsn, 1024, // out &len, SQL_DRIVER_NOPROMPT);

    In Win32 and MCBS, the above code (right or wrong) worked ok for the past 16 odd years.  The connection was made, *then* the buffer was filled with the completed string.  In x64 and UNICODE however it fails.  It appears that the buffer is being cleared prior to attempting the connection.  Making a copy of the string similar to the example below got the code working again, and seems to prove the theory.

    SQLTCHAR dsn[1024] = _T("DSN=MYDB;UID=FOO;PWD=BAR");
    SQLRETURN n;
    SQLSMALLINT len;
    SQLTCHAR* copy;
    
    copy = (SQLTCHAR*)_tcsdup((PCTSTR)dsn);
    n = SQLDriverConnect(hdbc, 
                         NULL, 
                         copy, SQL_NTS, // in
                         dsn, 1024,    // out
                         &len,
                         SQL_DRIVER_NOPROMPT);
    free(copy);


    This signature unintentionally left blank.

    • Označen jako odpověď Nick F. _ 6. března 2012 17:10
    6. března 2012 17:09