none
Execute Linked server Query from Ado.net

    Question

  • I have SQL select query that retrieves data from two different DB's of different sql server instances.

    This query works fine, when run from sql management studio but when executed from Ado.net.

    It throws,

    login failed for user 'nt authority anonymous logon'.

    Please find below code for reference.

              

    SqlConnectionconn = newSqlConnection(connectionstring);

               

    SqlDataAdapterdatareader = null;

               

    DataTabletable = newDataTable();

               

    try


                {

                   

    // Open the connection


                    conn.Open();

                   

    // Pass the connection and get query results


                    datareader =

    newSqlDataAdapter(query, conn);

                    datareader.Fill(table);

     
    Tuesday, January 22, 2013 12:19 PM

Answers

  • Hi, the following example creates a mapping to make sure that the Windows user Domain\Mary (local account) connects through to the linked server Accounts by using the login MaryP (linked server account) and password d89q3w4u.

    EXEC sp_addlinkedsrvlogin 'Accounts', 'false', 'Domain\Mary', 'MaryP', 'd89q3w4u'

    Please change the above code with your linked server name, local account, linked server account and password. For more detail information, please refer to the following link:

    sp_addlinkedsrvlogin (Transact-SQL)
    http://msdn.microsoft.com/en-us/library/ms189811(v=sql.105).aspx



    Allen Li
    TechNet Community Support

    Monday, January 28, 2013 1:58 AM

All replies

  • Tuesday, January 22, 2013 12:26 PM
  • I would have a look at the security configuration you are using for the linked server. In your connection string are you using a trusted connection, if so the linked server sounds like it is passing through the logon details of the application. Once idea is to force the linked server to use a different login and see if that works.
    Tuesday, January 22, 2013 8:54 PM
  • HI ,

    I am trying to execute a linked server query using Ado.net with userid and pwd

    I am using below code to execute the same.

    SqlConnection conn = new SqlConnection(
                    "Data Source=servername;Initial Catalog= master;User ID=sa;Password=mypwd");
                SqlDataAdapter datareader = null;
                DataTable table = new DataTable();
                    // Open the connection
                    conn.Open();

                    // Pass the connection and get query results
                    datareader = new SqlDataAdapter(mysqlquery, conn);
                    datareader.Fill(table);
    I am able to open connection successfully but I get below error when this code is executed
    >>                datareader.Fill(table);

    {"The OLE DB provider \"SQLNCLI10\" for linked server
    \"INTERMEDIATEDB\" reported an error. Authentication failed.\r\nCannot initialize the data source object of OLE DB provider \"SQLNCLI10\" for linked server \"INTERMEDIATEDB\"."}

    mysqlquery which is passed as argument works fine when i manually open sqlmanagement studio new query window and execute.

    Any help?

    Wednesday, January 23, 2013 2:13 PM
  • Hi Anil,

    Please log in SQL Server via SQL Server Management Studio with “sa” account and check whether we can execute the T-SQL commands. And then please open linked server property and make sure “sa” account maps to an account which has the required permission on the linked server.


    Allen Li
    TechNet Community Support

    Thursday, January 24, 2013 2:08 AM
  • Hi

    Thanks for the inputs.

    Please log in SQL Server via SQL Server Management Studio with “sa” account and check whether we can execute the T-SQL commands

    >> I did above step it works fine,

    make sure “sa” account maps to an account which has the required permission on the linked server

    >> Can you explain in detail like, how I can achieve this.

    Thanks

    Friday, January 25, 2013 8:16 AM
  • Hi, the following example creates a mapping to make sure that the Windows user Domain\Mary (local account) connects through to the linked server Accounts by using the login MaryP (linked server account) and password d89q3w4u.

    EXEC sp_addlinkedsrvlogin 'Accounts', 'false', 'Domain\Mary', 'MaryP', 'd89q3w4u'

    Please change the above code with your linked server name, local account, linked server account and password. For more detail information, please refer to the following link:

    sp_addlinkedsrvlogin (Transact-SQL)
    http://msdn.microsoft.com/en-us/library/ms189811(v=sql.105).aspx



    Allen Li
    TechNet Community Support

    Monday, January 28, 2013 1:58 AM