Sql-server – How to perform the dreaded double hop with linked servers

ms accesssql-server-2008

The situation:
I have SERVER1, SERVER2, ACCESSDB on SERVER3.

I have successfully created a linked server connection from SERVER1 to SERVER3's ACCESSDB.

I want to be able to access the linked server on SERVER1 from SERVER2 (aka the double hop).

When I log into SERVER2 I get the good ol – "dont have permissions" issue when I test the connection. I checked SERVER3 logs and it says the incoming connection is ANONYMOUS. I need to be able to pass some (any) credentials to SERVER3 from SERVER2.

NO, I cannot use a SQL account. I have to use Windows Auth.

Yes, I need to use KERBEROS and set up SPNs. That's where my question comes in. How do I properly set up KERBEROS and delegation?

I want to be able to run

select auth_scheme from sys.dm_exec_connections where session_id=@@SPID

from SERVER2 and have it return 'KERBEROS' and not 'NTLM'.

What specific commands do I need to run to properly set up KERBEROS/SPNs? How do I create this "chain"?

FYI – Both instances are NAMED instances on SQL2008 R2 Standard (x64).

Best Answer

Security Account Delegation

And for SQL Server 2000 but still valid too

Basically

Configure the server in AD
Set up the SPN
Use sp_addlinkedsrvlogin so useself = true

Related Solutions

Sql-server – Simple Implementation of SQL Server 2008 Encryption

Q1a: Is the master key password created per DB instance?

A1a: Assuming the question really means "Is the master key created per DB?"" then answer is Yes. Each DB has an different master key. there is also a thing called the service master key, which is per SQL Server instance.

Q1b: When I backup that DB (.bak) will I be able to restore the DB to another Server2?

A1b: Yes. The master key in the restored database can be open using the original password. Then the Server2 service master key encryption can be added to the restored DB master key.

Q2: Do I need to backup the certificate, master key or symmetric key if I want to restore to another server?

A2: No. All these objects are part of the database and they are restored along with the database. Specific needs to backup individual keys may arise from operational requirements (eg. key escrow).

Q3: When should the symmetric key be opened?

A3: Keys that require opening have to be opened in the session. Once opened, they stay open until explicitly closed or until the session disconnects. An 'open' key is 'open' only in the session that opened it.

Q4: Should I worry about who can open and close the symmetric key? ...

A4: Now this is the real question. You have two alternatives really, which correspond to two distinct scenarios:

Scenario A: when the service needs to access encrypted data without asking the user for passwords to the data. This is the vast majority of the cases. In this case the service needs to open the keys somehow. the solution is that the SQL Server uses the service master key to encrypt the database master key and the database master key is used to encrypt the certificate's private key and the private key is used to encrypt a symmetric key and the symmetric key encrypts the data. The SQL Server itself can reach any data following this chain, because it has access to the service master key. In this case the data is cryptographically protected only against accidental media loss: a lost laptop with the database on it, or an improperly disposed HDD with database of backup files on it etc. For all other threats, the data is not cryptographically protected, is only protected by the access control: since the SQL Server itself can decrypt the data w/o needing a password from the user, any user with sufficient privileges can access the data w/o knowing the password. In other words, a compromised ASP application may allow access to the encrypted data. As a note, scenarios in which the root of encryption is some secret stored on the ASP web application itself are just a (badly designed) variation on this and do not change anything.

Scenario B: when the service requires the user to provide a password. The password must come from the end user. In a web application, the user must type the password in a form in the browser, it gets sent over SSL channel to the ASP application, which passes it to the SQL Session (again on an SSL channel) and SQL can now decrypt the data using this password. This scenario is typical for multi-tenant applications in which tenants provide the data access password. In this scenarios the data is cryptographically secured against unauthorized access, because the SQL Server itself, nor the intermediate ASP web application, simply do not know the password. Even for a syadmin user with all privileges it would be impossible to read the data. Data can be moved at will and remains just as unscrutable, as it can be, again, only be read by the end-user that has knowledge of the password at the root of the encryption chain. Note that any 'shortcut' deviation in this scenario in which the password is 'saved' somewhere intermediately and not provided by the end-user this scenario degrades immediately to the first Scenario A.

A mandatory read for you: Encryption Hierarchy.

Sql-server – Why is this stored procedure for linked server job failing in SQL Server Agent

Although you don't mention it in the question, I assume that the reason you're using an SSIS package rather than simply executing the stored proc from a SQL Agent job is that there are other steps in the process. My other assumption is that you schedule the SSIS package using a SQL Agent job.

The first error message implies that you are creating an OLEDB connection to your Access database but that the database could not be found. It's possible that this is a security problem, and that the Windows account under which the SSIS package is running does not have permissions to the file system location where the Access database is stored.
The second error message seems to back this up - when executing the SP, the linked server connection could not be opened.

To confirm this as the issue, you need to try running the SP in SSMS using the same account under which the SQL Agent service runs.

If this is the problem, you will need either to change the permissions of the SQL Agent service account to allow access to the target location, or copy the access db to a location that the account can access (possibly the local disk) before running the package.

EDIT - in response to additional details:

Now that permission and Networked Drive are mentioned, I think this may be the problem. However, I am still confused as to why I am able to manually execute the Stored Procedure from within SQL Server with the Access DB on a Network Drive?

The Acess linked server is configured to use the security credentials of the account executing the query. When you execute the procedure from SQL Server Management Studio, you are doing so in the context of your Windows account, which must have permissions to the network share.
When the same query is executed by the SQL Agent service, it uses the security context of the service account under which the service is running, which does not have access to the share.

Finally, how can I apply this suggestion? Meaning where in SQL Server do I configure this feature:

change the permissions of the SQL Agent service account to allow access to the target location

You can find out and change the account under which the SQL Agent service is running in the SQL configuration manager (should be found under Start > All Programs > Microsoft SQL Server 2008 > Configuration Tools. Find "SQL Server Agent" in the list of SQL Server Services and see the "Log On As" column). You may need to switch this to a domain account to get access to the share.

Best Answer

Related Solutions

Sql-server – Simple Implementation of SQL Server 2008 Encryption

Sql-server – Why is this stored procedure for linked server job failing in SQL Server Agent

Related Question