1

I can currently connect to my SQL Server and query any database I want to directly.

The problem is when I want to query a linked server. I cannot directly reference the linked servers name in the connect() method and I have to connect to a local database first and then run an OPENQUERY() against the linked server.

This seams like a odd work around. Is there a way to query the linked server directly (from my research you cannot connect directly to a linked server) or at least connect to the server without specifying a database where I can then run the OPENQUERY() for anything without having to first connect to a database?

Example of what I have to do currently:

import pyodbc

ex_value = "SELECT * FROM OPENQUERY(LinkedServerName,'SELECT * FROM LinkedServerName.SomeTable')"
# I have to connect to some local database on the server and cannot connect to linked server initially.
odbc_driver, server, db = '{ODBC Driver 17 for SQL Server}', 'MyServerName', 'LocalDatabase'

with pyodbc.connect(driver=odbc_driver, host=server, database=db, trusted_connection='yes') as conn:
    conn.autocommit = False
    cursor = conn.cursor()
    cursor.execute(ex_value)
    tables = cursor.fetchall()

    for row in tables:
        print('Row: {}'.format(row))

    cursor.close()
Mike - SMT
  • 14,784
  • 4
  • 35
  • 79
  • 1
    A linked server is not a database or a sql server. It is a server object that allows you to query remote databases. So yes what you are doing is what you have to do. Or make your connection directly to the remote server when that is the only data you want. The performance would be better going directly to the source instead of extra hops via linked server. – Sean Lange Oct 29 '19 at 20:40
  • @SeanLange I cannot connect directly to the linked server because I am building an application for many people to use and we do not want to manage 100+ user credentials. We have machine ID's on the SQL Server that tie into windows authentication so we only have to manage user groups within SQL Server. If what I am doing now is my only option I guess that is fine. I will create a dummy database with nothing in it to connect to then. – Mike - SMT Oct 29 '19 at 20:59
  • That is probably the best course of action given the scenario. – Sean Lange Oct 29 '19 at 21:00
  • 1
    @SeanLange I thought it might be but one could hope for a better solution :D. Thanks for the info. – Mike - SMT Oct 29 '19 at 21:01

1 Answers1

1

As Sean mentioned, a linked server is just a reference to another server that's stored within the local server.

You do not need to manage 100+ user credentials though. If you have the users using Windows auth, and you have Kerberos working between the servers, the linked server can just impersonate you when it connects to the other server via the linked server definition.

Then you can use either 4 part names to refer to objects on the other server, or use OPENQUERY when you want more control over what gets executed where.

Finally, if they're both SQL Servers and both use the same collation, make sure you set the linked server option to say they are collation compatible. That can make a major difference to your linked server performance. I regularly see systems where that isn't set and it should be.

Greg Low
  • 1,526
  • 1
  • 4
  • 4
  • I already mentioned I am using windows authentication and using OPENQUERY. Also we do not have kerberos nor can I get it as I do not control the server. The linked servers are all Oracle and DB2. Due to an issue with Oracle data types I have no choice but to use OPENQUERY. – Mike - SMT Oct 30 '19 at 16:20
  • Is there any reason the users can't just connect to tempdb if all you need is another DB for them to connect to? But without Kerberos, I can't imagine any way that your Windows creds are going to work across the network to the linked servers. It's needed for delegation to work. – Greg Low Oct 31 '19 at 04:41
  • It works just fine. Windows authentication is used to grant access to the SQL Server and authentication groups are used to grant access to specific databases using the internal machine ID's used to connect to linked servers. I just don't like the round about way I have to query the linked servers. It is required tho as our company does not want to maintain user credentials to multiple servers as personal access is reset every 3 months. That is why we have to go this route. – Mike - SMT Oct 31 '19 at 12:59