none
Cannot initialize the data source object of OLE DB provider "SQLNCLI10" for linked server "".

    Question

  • There is a data-source on a windows 7 laptop running Developer - and in one case the MSDN version of Ent SQL 08.  There is a table that the developer works with which we would like to subscribe to with the domain primary SQL Server.  For some reason, the error above appears.  The developer is on the domain although is often away using VPN.  The service account is common to both machines.  This is a distributed query.  The linked server appears in both the client and the server where windows 7 is the client and Windows 08 is the server.  Both run SQL 08.  Windows 7 did not have RPC running, I started it - nothing - rebooted, and still nothing.  From the Windows 7 machine, access to the data on the Windows 08 machine (also running ent 08) is available.  It is the WIndows 7 that will not allow the connection.  We can remote into the machine from the server without a problem so RPC does not appear to be the problem.  Not sure where else to look.
    Wednesday, October 14, 2009 2:36 PM

Answers

  • "

    My suspicion is the user installed using the local administrator account, not the sysadmin account - although, I am aware at this point that the user has switched the service to start up using the service account

    "

    The installation part should be isolated from the security of SQL server. You will need admin priviliges to install SQL server anyway but any user can be a SYSADMIN on SQL box. SQL server designates windows group "builtin administrators" as sysadmin by default.

    Now looking again at the code you posted (I may missed a part of it before) :

    I can see the linked server is CLIENTPC , I used to think that you only need to make server08 as the linked server but now it looks like the instance on windows 7 is the destination linked server and YES firewall could be the issue.

    I've Windows 7 as well , so you can follow these steps to allow port 1433 if the instance is default

    - Run wf.msc to launch Windows firewall snapin

    - On inbound rules , right click and choose "new role"

    - choose port

    - enter 1433

    - Allow connection

    - Choose where you want his rule to apply (domain , private , public).

    - name the rule and click finish.


    Then try your linked server and let us know

    HTH
    Please mark as answer if you think this answers your questions
    • Marked as answer by Crakdkorn Friday, October 16, 2009 10:21 PM
    Friday, October 16, 2009 3:46 PM
  • "

    Login failed for user 'domainname\username'

    "


    Add that login in your destination server.


    "

    Name resolution for the name dns.msftncsi.com timed out after none of the configured DNS servers responded.

    "

    Sounds like a network issue , contact you network admin.

    HTH



    Please mark as answer if you think this answers your questions
    • Marked as answer by Crakdkorn Sunday, February 03, 2013 6:27 PM
    Friday, October 16, 2009 9:13 PM

All replies

  • How did you configure the access credentials ?

    can you please try to create the linked server from scratch using T-SQL then try to run the distributed query.

    EXEC master.dbo.sp_addlinkedserver @server = N'sql08', @srvproduct=N'SQL Server'
    GO
    
    -- I'm using remote logins here
    EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'sql08', @locallogin = NULL , @useself = N'False', @rmtuser = N'user', @rmtpassword = N'password'
    GO
    
    Select * from sql08.master.sys.databases
    GO


    Please post the queries and all output of the two commands.

    THX

    Please mark as answer if you think this answers your questions
    Wednesday, October 14, 2009 8:16 PM
  • OLE DB provider "SQLNCLI10" for linked server "CLIENTPC" returned message "Unable to complete login process due to delay in opening server connection".

    Msg 7303, Level 16, State 1, Line 1

    Cannot initialize the data source object of OLE DB provider "SQLNCLI10" for linked server "CLIENTPC".

    /****** Object: LinkedServer [CLIENTPC] Script Date: 10/14/2009 11:36:32 ******/

    IF

     

    EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'CLIENTPC')

    EXEC

     

    master.dbo.sp_dropserver @server=N'CLIENTPC', @droplogins='droplogins'

    GO

    EXEC

     

    master.dbo.sp_addlinkedserver @server = N'CLIENTPC', @srvproduct=N'SQL Server'

    GO

    EXEC

     

    master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'CLIENTPC', @locallogin = NULL , @useself = N'False', @rmtuser = N'domainname\serviceaccount, @rmtpassword = N'svcacctpassword'

    GO

    Select

     

    * from [CLIENTPC].master.sys.databases

    Friday, October 16, 2009 11:42 AM
  • Something that crosses my mind - the user installs SQL not the SYSADMIN.  My suspicion is the user installed using the local administrator account, not the sysadmin account - although, I am aware at this point that the user has switched the service to start up using the service account.  I am wondering if there is an issue with trust. (I always wonder if there is an issue with trust.)
    Friday, October 16, 2009 11:47 AM
  • "

    OLE DB provider "SQLNCLI10" for linked server "CLIENTPC" returned message "Unable to complete login process due to delay in opening server connection".

    "

     

    Kindly have a look here

    http://blogs.msdn.com/sql_protocols/archive/2005/09/28/474698.aspx

     

    Quote

    "
    A successful TCP connection from SqlClient to the server was broken due to a TCP keep-alive heartbeat failure (typically indicating problems with the underlying network infrastructure)

    "
    Well , I can suggest to review your network settings then. You are saying you use VPN so there maybe a catch here.

     

    Could you please try creating the linked server using SQL account ?


    THX

    Please mark as answer if you think this answers your questions
    Friday, October 16, 2009 11:56 AM
  • We are treating this as a Windows 7 problem, I hope.  It seems it may well be something in the firewall as I see you mention TCP.   The configuration of the Windows 7 firewall seems to be unnecessarily complicated.  The RPC for the client is On - I think this is necessary but not 100% sure.  I am not familiar with the necessary settings for this new firewall.  In XP, it was simply a matter of enabling port 1433.  In Windows 7, there does not appear to be a screen that allows you to automatically configure 1433 as open on the client.  Can we move the discussion a bit to cover this aspect?   The client did not have the difficulty using XP. (SP3 64 bit)
    Friday, October 16, 2009 3:27 PM
  • "

    My suspicion is the user installed using the local administrator account, not the sysadmin account - although, I am aware at this point that the user has switched the service to start up using the service account

    "

    The installation part should be isolated from the security of SQL server. You will need admin priviliges to install SQL server anyway but any user can be a SYSADMIN on SQL box. SQL server designates windows group "builtin administrators" as sysadmin by default.

    Now looking again at the code you posted (I may missed a part of it before) :

    I can see the linked server is CLIENTPC , I used to think that you only need to make server08 as the linked server but now it looks like the instance on windows 7 is the destination linked server and YES firewall could be the issue.

    I've Windows 7 as well , so you can follow these steps to allow port 1433 if the instance is default

    - Run wf.msc to launch Windows firewall snapin

    - On inbound rules , right click and choose "new role"

    - choose port

    - enter 1433

    - Allow connection

    - Choose where you want his rule to apply (domain , private , public).

    - name the rule and click finish.


    Then try your linked server and let us know

    HTH
    Please mark as answer if you think this answers your questions
    • Marked as answer by Crakdkorn Friday, October 16, 2009 10:21 PM
    Friday, October 16, 2009 3:46 PM
  • Much better.   Now it is simply a login error and no longer related to SQL Native Client.   At this point, it is
    "Login failed for user 'domainname\username'"

    Also have
    "Name resolution for the name dns.msftncsi.com timed out after none of the configured DNS servers responded."

    Is it required to list the CLIENTPC as a trusted computer in SBS 2003 (if you are familiar with SBS) - the warning about DNS seems curious to me.


    Here are the details:

    - System

      - Provider

       [ Name]  Microsoft-Windows-DNS-Client
       [ Guid]  {1C95126E-7EEA-49A9-A3FE-A378B03DDB4D}
     
       EventID 1014
     
       Version 0
     
       Level 3
     
       Task 0
     
       Opcode 0
     
       Keywords 0x4000000000000000
     
      - TimeCreated

       [ SystemTime]  2009-10-16T20:13:41.421391500Z
     
       EventRecordID 14986
     
       Correlation
     
      - Execution

       [ ProcessID]  1176
       [ ThreadID]  5720
     
       Channel System
     
       Computer clientpc.mydomain.local
     
      - Security

       [ UserID]  S-1-5-20 
     

    - EventData

      QueryName dns.msftncsi.com
      AddressLength 16
      Address 02000035C0A810020000000000000000

    Friday, October 16, 2009 8:28 PM
  • "

    Login failed for user 'domainname\username'

    "


    Add that login in your destination server.


    "

    Name resolution for the name dns.msftncsi.com timed out after none of the configured DNS servers responded.

    "

    Sounds like a network issue , contact you network admin.

    HTH



    Please mark as answer if you think this answers your questions
    • Marked as answer by Crakdkorn Sunday, February 03, 2013 6:27 PM
    Friday, October 16, 2009 9:13 PM
  • This is good for now, thanks. 

    Possible ISA solution seems to be here http://articles.techrepublic.com.com/5100-22_11-5545629.html 

    Thanks also to Dave Pinal whose advice was to review all the standard tricks we use to keep the SQL alive:

    http://blog.sqlauthority.com/2009/05/21/sql-server-fix-error-provider-named-pipes-provider-error-40-could-not-open-a-connection-to-sql-server-microsoft-sql-server-error/

    In my case a combination of adding SQLBrowser to the firewall and deleting and recreating the alias appears to be a big part of the solution.
    • Edited by Crakdkorn Monday, October 19, 2009 7:09 PM Found better solution
    Friday, October 16, 2009 10:40 PM