May 7, 2018 at 1:24 pm
I have a linked server connection set up to our active directory domain controller. I also have an SSIS job that runs via SQL Server Agent on a schedule to pull/sync data from this server with OPENQUERY statements to local tables on our SQL Server. This all works fine and has for years. My issue comes in that our Active Directory security is being "hardened" and now requires fully secure connections everywhere. In testing, the following error is displayed in Active Directory logs any time this job is run:
The following client performed a SASL (Negotiate/Kerberos/NTLM/Digest) LDAP bind without requesting signing (integrity verification), or performed a simple bind over a clear text (non-SSL/TLS-encrypted) LDAP connection.
This is not an area I am very familiar with and wanted to see if anyone had any suggestions. If I run the same query in Management Studio directly it will work without error, so I assume the problem lies somewhere in SSIS, SQL Agent and how permissions are passed. All accounts being used have for rights to query the information, the error is that it does not like the the rights are passed to Active Directory without signing.
Also, I am in process of getting a Certificate for the SQL Server and adding that under the Network Configuration of SQL, but that is not complete yet and I am not sure that would have any affect with an outbound connection to AD.
Any suggestions?
May 8, 2018 at 6:04 am
My first thought is to look at the configuration of the Linked Server, although I can't say I can recall any particular setting that would affect this. However, as you are using OPENQUERY to begin with, why have a permanent Linked Server? You could use OPENROWSET instead, and specify the connection string directly each time. Not sure if there's anything in a connection string that could help, but maybe?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 8, 2018 at 6:47 am
elanders - Monday, May 7, 2018 1:24 PMI have a linked server connection set up to our active directory domain controller. I also have an SSIS job that runs via SQL Server Agent on a schedule to pull/sync data from this server with OPENQUERY statements to local tables on our SQL Server. This all works fine and has for years. My issue comes in that our Active Directory security is being "hardened" and now requires fully secure connections everywhere. In testing, the following error is displayed in Active Directory logs any time this job is run:The following client performed a SASL (Negotiate/Kerberos/NTLM/Digest) LDAP bind without requesting signing (integrity verification), or performed a simple bind over a clear text (non-SSL/TLS-encrypted) LDAP connection.
This is not an area I am very familiar with and wanted to see if anyone had any suggestions. If I run the same query in Management Studio directly it will work without error, so I assume the problem lies somewhere in SSIS, SQL Agent and how permissions are passed. All accounts being used have for rights to query the information, the error is that it does not like the the rights are passed to Active Directory without signing.Also, I am in process of getting a Certificate for the SQL Server and adding that under the Network Configuration of SQL, but that is not complete yet and I am not sure that would have any affect with an outbound connection to AD.
Any suggestions?
Did they just implement LDAP signing? That's what it looks like from the error. I think you may need a certificate. Check the following:
Domain controller: LDAP server signing requirements
Sue
May 8, 2018 at 7:49 am
Sue_H, yes it is LDAP signing that is being turned on. I should have clarified that, and they did not actually turn it on yet, but enabled events that would show what "would break".
I found that the underlying problem was that I set up the linked server to "Be made using this security context: _____" and specified the account with access. Apparently when doing it this way the credentials are passed through simply. I was able to fix it by "using the login's current security context" in the linked server properties, so that access was integrated windows. Since I still needed to use a specific account to run the job and queries, I set up a proxy account with the appropriate credential to run the SSIS Job via SQL agent.
sgmunson, I imagine using OPENROWSET with integrated security would have also worked.
Thank you for the suggestions.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply