none
OpenrowSet throws exception-Login timeout expired RRS feed

  • Question

  • ola, when i execute below query then it throws following exception

    SELECT
    col1, col2, col3
    INTO
      #tmp1
    FROM
      OPENROWSET(
        'SQLNCLI',
        'Server=MyServer,PortNumber;Trusted_Connection=yes;',
        'exec usp_Search @companyID=10'
    )

    select * from #tmp1;

    exception

    OLE DB provider "SQLNCLI11" for linked server "(null)" returned message "Login timeout expired".
    OLE DB provider "SQLNCLI11" for linked server "(null)" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".
    Msg 10060, Level 16, State 1, Line 0
    TCP Provider: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.


    Monday, December 9, 2019 1:18 PM

All replies

  • Do not you have any problem to connect remote server via Windows Authentication? Can you connect vis SSMS?


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, December 9, 2019 1:31 PM
    Answerer
  • Jut tested .. and it works just fine

    select * from openrowset('SQLNCLI','Server=SRV,portnumber;Trusted_Connection=YES;','exec dbo.sp_who') AS a


    EXEC sp_configure 'show advanced options', 1;
    GO
    RECONFIGURE;
    GO
    EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
    GO
    RECONFIGURE;


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence


    Monday, December 9, 2019 1:41 PM
    Answerer
  • Jut tested .. and it works just fine

    select * from openrowset('SQLNCLI','Server=SRV,portnumber;Trusted_Connection=YES;','exec dbo.sp_who') AS a


    EXEC sp_configure 'show advanced options', 1;
    GO
    RECONFIGURE;
    GO
    EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
    GO
    RECONFIGURE;



    this also gives the same error
    Monday, December 9, 2019 2:50 PM
  • Do not you have any problem to connect remote server via Windows Authentication? Can you connect vis SSMS?



    sorry im using sql authentication only not windows authentication
    Monday, December 9, 2019 2:51 PM
  • So can you specify in OPENROWSET user and pass?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, December 10, 2019 5:22 AM
    Answerer
  • Hi , 

    Per your description,  I think one possibility  is that the issue may be related to connecting to the SQL Server Database Engine.

     

    Here are some suggestions which you can check :

    1) Make sure SQL Server Service is running. It will allow you to see your SQL Server/instance in the drop-down list of available SQL Server Servers.

    2) Check firewall settings.Firewall might be blocking port 1433 (SQL Server standard port for connections).

    3) Check if TCP/IP protocol is disabled for SQL Server protocols.

    4) Make sure your database engine is configured to accept remote connections

    5) If you are using a named SQL Server instance, make sure you are using that instance name in your connection strings. Make sure SQL Server browser service is running

    6) Make sure your login account has access permission on the database you used during login.

     

    For more details, you can refer to this article :

    https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/troubleshoot-connecting-to-the-sql-server-database-engine?view=sql-server-ver15

     

    Hope it can help you.

     

    Best Regards,

    Rachel

     


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, December 10, 2019 6:31 AM
  • Hi ,

     

    I am writing to follow up this thread with you. Have you solved your issue? If you have solved your issue ,in order to close this thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue.  Your contribution is highly appreciated.

     

    Best Regards,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, December 17, 2019 1:50 AM