none
Using OpenRowset with ApplicationIntent=READONLY

    Question

  • I have a client trying to use OpenRowset to run a stored procedure against a ReadOnly database.  We can run the stored procedure successfully against the database using the EXECUTE command directly.

    Here's their command:

    SELECT a.*  
    FROM OPENROWSET('SQLNCLI', 'uid:<userid>;pwd=<password>;driver={SQLServer};server=<server>;ApplicationIntent=READONLY',  
    'exec <stored procedure> <parameters>) AS a;  

    The login, server, and EXEC command are correct.  The issue seems to be using OpenRowset and/or ApplicationIntent=READONLY.

    Thanks

    Tuesday, April 16, 2019 12:02 PM

Answers

  • >>System.Data.SqlClient.SqlException (0x80131904): A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - No such host is known.) ---> System.ComponentModel.Win32Exception (0x80004005): No such host is known

    <<

    Hi Lanis,

    Per the error message above, the issue is more likely related connection issue. For this purpose, you could follow this support article to start your troubleshooting.

    Solving Connectivity errors to SQL Server

    Best Regards,

    Will


    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.

    Friday, April 19, 2019 8:53 AM
    Moderator

All replies

  • what is the error you're getting?

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, April 16, 2019 12:08 PM
  • I'll have to get that.  I'm kind of a middle-man here.
    Tuesday, April 16, 2019 12:23 PM
  • I'll have to get that.  I'm kind of a middle-man here.

    Any update for the issue? Have you got the detailed message from the operator?

    Best Regards,

    Will


    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.

    Thursday, April 18, 2019 9:29 AM
    Moderator
  • The following error occurs.  However, is there a general issue with running ApplicationIntent=READONLY in this context.  I thought I may have seen issues with this in other posts, but wasn't 100% clear on that.

    System.Data.SqlClient.SqlException (0x80131904): A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - No such host is known.) ---> System.ComponentModel.Win32Exception (0x80004005): No such host is known

       at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling, SqlAuthenticationProviderManager sqlAuthProviderManager)

       at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)

       at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)

       at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)

       at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)

       at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)

       at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)

       at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)

       at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)

       at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)

       at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)

       at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)

       at System.Data.SqlClient.SqlConnection.Open()

     <v:shapetype coordsize="21600,21600" filled="f" id="_x0000_t75" o:preferrelative="t" o:spt="75" path="m@4@5l@4@11@9@11@9@5xe" stroked="f"><v:shapetype coordsize="21600,21600" filled="f" id="_x0000_t75" o:preferrelative="t" o:spt="75" path="m@4@5l@4@11@9@11@9@5xe" stroked="f">  <v:stroke joinstyle="miter">
      <v:formulas>  <v:f eqn="if lineDrawn pixelLineWidth 0">
      <v:f eqn="sum @0 1 0">
      <v:f eqn="sum 0 0 @1">
      <v:f eqn="prod @2 1 2">
      <v:f eqn="prod @3 21600 pixelWidth">
      <v:f eqn="prod @3 21600 pixelHeight">
      <v:f eqn="sum @0 0 1">
      <v:f eqn="prod @6 1 2">
      <v:f eqn="prod @7 21600 pixelWidth">
      <v:f eqn="sum @8 21600 0">
      <v:f eqn="prod @7 21600 pixelHeight">
     <v:f eqn="sum @10 21600 0">
    </v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:formulas>
     <v:path gradientshapeok="t" o:connecttype="rect" o:extrusionok="f">
    <o:lock aspectratio="t" v:ext="edit">
    </o:lock></v:path></v:stroke></v:shapetype> <v:shape alt="" id="Picture_x0020_1" o:spid="_x0000_i1025" style="width:472.5pt;height:174pt;" type="#_x0000_t75">
    <v:imagedata o:href="cid:image001.png@01D4F525.A1DB5CD0" src="file:///C:/Users/bossman/AppData/Local/Temp/msohtmlclip1/01/clip_image001.png">
    </v:imagedata></v:shape>

      <v:stroke joinstyle="miter">
      <v:formulas>  <v:f eqn="if lineDrawn pixelLineWidth 0">
      <v:f eqn="sum @0 1 0">
      <v:f eqn="sum 0 0 @1">
      <v:f eqn="prod @2 1 2">
      <v:f eqn="prod @3 21600 pixelWidth">
      <v:f eqn="prod @3 21600 pixelHeight">
      <v:f eqn="sum @0 0 1">
      <v:f eqn="prod @6 1 2">
      <v:f eqn="prod @7 21600 pixelWidth">
      <v:f eqn="sum @8 21600 0">
      <v:f eqn="prod @7 21600 pixelHeight">
     <v:f eqn="sum @10 21600 0">
    </v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:f></v:formulas>
     <v:path gradientshapeok="t" o:connecttype="rect" o:extrusionok="f">
    <o:lock aspectratio="t" v:ext="edit">
    </o:lock></v:path></v:stroke></v:shapetype> <v:shape alt="" id="Picture_x0020_1" o:spid="_x0000_i1025" style="width:472.5pt;height:174pt;" type="#_x0000_t75">
    <v:imagedata o:href="cid:image001.png@01D4F525.A1DB5CD0" src="file:///C:/Users/bossman/AppData/Local/Temp/msohtmlclip1/01/clip_image001.png">
    </v:imagedata></v:shape>

    Thursday, April 18, 2019 11:27 AM
  • That error has nothing to do with ApplicationIntent, but it means that the server given in the connection string does not exist, or is not reachable.

    This could occur for any number of reasons. Firewalls, the instance not running, fat-fingering on the server name, network segmenting etc.

    And for that matter it could happen if you have

    Server=server:ApplicationIntent=Readonly

    That is, you have the incorrect delimiter after the server name.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, April 18, 2019 12:49 PM
  • >>System.Data.SqlClient.SqlException (0x80131904): A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - No such host is known.) ---> System.ComponentModel.Win32Exception (0x80004005): No such host is known

    <<

    Hi Lanis,

    Per the error message above, the issue is more likely related connection issue. For this purpose, you could follow this support article to start your troubleshooting.

    Solving Connectivity errors to SQL Server

    Best Regards,

    Will


    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.

    Friday, April 19, 2019 8:53 AM
    Moderator