locked
query a remote database with sql native client fails RRS feed

  • Question

  • Hi there,

    I'm using a sql native client 2008 to query a remote database running within another trusted domain. This fails. No query is being made. When I run the script with the same credential in the domain the database is running in, it does work. The credential consist of a AD domain account from the domain the database is running in.

    I can set up a connection using telnet to the remote server in the other domain through port 1433

    Pls help.

    Regards,

    Chris


    • Edited by Chrisszs Friday, October 12, 2012 5:58 PM
    Friday, October 12, 2012 5:57 PM

Answers

  • So it seems that you are able to connect, but then something else happens. What we don't know since we don't know what is that script.

    I would use Profiler to see what actions SQL Server sees. The trace would need to include both Audit and SQL events. If you don't have permissions to run Profiler, talk with your DBA.

    Reviewing the script to see where it produces this message could also be worth doing.

    The connection string looks a little funny. Change it to:

    con.ConnectionString = "Provider=SQLNCLI10;Data Source=<server1>\INST01;Failover Partner=<server2>\INST01;Initial Catalog=<cat>;Integrated Security=SSPI"


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Maggie Luo Tuesday, October 23, 2012 6:58 AM
    Tuesday, October 16, 2012 9:43 PM

All replies

  • And "fails" means? If you get an error message, please include it.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, October 12, 2012 6:47 PM
  • Hello Chris,

    And do that account have permissions to login to the SQL Server in the trusted domain? That's required, just beeing a member of a domain is not enough. Contact the DBA of that SQL Server and ask him to grant access for that account.


    Olaf Helper

    Blog Xing

    Sunday, October 14, 2012 8:07 AM
  • Like I said, when I run the script with the same credential in the domain the database is running in, it does work.
    Sunday, October 14, 2012 9:09 PM
  • Like I said, when I run the script with the same credential in the domain the database is running in, it does work.

    Are you getting an error message?  If so please post the error message here so that we can better help you.  In the case of unexpected results, describe the expected results versus what was actually returned.


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    Sunday, October 14, 2012 10:50 PM
  • with the same credential in the domain
    It's may the same account, but not the same SQL Server and you need permissions on the SQL Server you want to connect to.

    Olaf Helper

    Blog Xing

    Monday, October 15, 2012 6:02 AM
  • Hi, it's the same query to the same sql server using the same credential. Only the location the script is run from differs. That is from another computer that is in an other (trusted-)domain.
    Monday, October 15, 2012 9:15 AM
  • Can you please post the error message?

    Also, can you clarify what you mean with the "same credential"? Do you use Windows authentication and it is the same Windows user? Or do you use SQL authentication?

    Can you post the command line you are using?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, October 15, 2012 9:40 PM
  • I don't have a proper error message, besided the one the script reports back (which is something like it couldn't do the query).

    Like I said it's a AD domain account so I use Windows authentication.

    Monday, October 15, 2012 10:32 PM
  • I don't have a proper error message, besided the one the script reports back (which is something like it couldn't do the query).

    Can you please, please share that error message?

    You mention "script". Previously you only said that you were using SQL Native Client, why I assume that you were using SQLCMD or somesuch. Now you have a script? What script? Can you share the code?

    What happens if you say

    SQLCMD -S yourservernamehere

    Please understand that in order to help you we are reliant on the information you give us. If you don't give us enough information, we can only make guesses out of blue.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Tuesday, October 16, 2012 8:03 AM
  • I did mention the script in my first post, but appearently not clearly enough. ;) The script fires the concerning query. It works within the domain where the database is running, but not outside from another trusted domain, although I am running the script with the same AD domain account all the time.

    The error the script shows (only from the remote trusted domain) is: "Server running?, authorization ok?". I don't think that will give you much info. It's the script that reports that message. From within the domain I get proper results back from the script.

    The part in the script you are looking for is this I think ( I can't  share the whole code unfortunately): 

    con.ConnectionString = "Provider=SQLNCLI10;Data Source=<server1>\INST01;Failover Partner=<server2>\INST01;Initial Catalog=<cat>;Trusted_Connection=yes;integratedSecurity=true"

    SQLCMD -S <server1>\INST01 works also. I get a comandline like this-->  "1)"

    Does that mean authetication works? Or only that a connection can be made? The latter I'd already tested using telnet.






    • Edited by Chrisszs Tuesday, October 16, 2012 2:19 PM
    Tuesday, October 16, 2012 10:49 AM
  • SQLCMD -S yourservernamehere works also. I get a comandline like this-->  "1)"

    Does that mean authetication works? Or only that a connection can be made? The latter I'd already tested using telnet.

    The "1)" prompt means you'e successflly connected (authentication worked).  Is the "yourservernamehere" value exactly the same as the Data Source value in the connection string ("<server1>\INST01")?


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    Tuesday, October 16, 2012 12:33 PM
  •  Is the "yourservernamehere" value exactly the same as the Data Source value in the connection string ("<server1>\INST01")?


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    Yes it is. I will correct my previous post.
    Tuesday, October 16, 2012 2:13 PM
  • Hi,

    Please, try provider SQLNCLI10.1 instead of SQLNCLI10


    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    Tuesday, October 16, 2012 2:35 PM
  • So it seems that you are able to connect, but then something else happens. What we don't know since we don't know what is that script.

    I would use Profiler to see what actions SQL Server sees. The trace would need to include both Audit and SQL events. If you don't have permissions to run Profiler, talk with your DBA.

    Reviewing the script to see where it produces this message could also be worth doing.

    The connection string looks a little funny. Change it to:

    con.ConnectionString = "Provider=SQLNCLI10;Data Source=<server1>\INST01;Failover Partner=<server2>\INST01;Initial Catalog=<cat>;Integrated Security=SSPI"


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Maggie Luo Tuesday, October 23, 2012 6:58 AM
    Tuesday, October 16, 2012 9:43 PM