February 12, 2024 at 12:58 pm
So I have a DB on a remote server I do ADSI queries into AD with. The queries work when on my old laptop using SSMS connecting to the remote server and also work on the remote server itself but when I try to run the query on my new laptop using SSMS I get the following error:
Msg 7321, Level 16, State 2, Procedure vw_AD, Line 3 [Batch Start Line 0]
An error occurred while preparing the query "SELECT sAMAccountName,distinguishedName,userPrincipalName,givenName,initials,sn,title, mail,manager,telephoneNumber, mobile, employeeID,department,company,physicalDeliveryOfficeName,streetAddress, l, st, postalCode,displayname,facsimileTelephoneNumber
FROM 'LDAP://OU=XXX,OU=XXXX,OU=XXX,DC=XX,DC=XXX,DC=XXX,DC=XXX'
WHERE objectCategory = 'Person' AND objectClass = 'user'
AND 'userAccountControl:1.2.840.113556.1.4.803:'<>2" for execution against OLE DB provider "ADSDSOObject" for linked server "ADSI".
XXX in place of actual OU and DC info. It is driving me crazy as obviously the query and permissions is good if I can query on my old laptop and on the server but not on the new machine. Any ideas?
February 12, 2024 at 10:14 pm
Possibly a dumb question but is your new laptop domain joined and on the corporate network (VPN or on site)?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
February 14, 2024 at 11:29 am
Yes, it is joined to the domain, and I have tried both on site and VPN no difference. Everything else in SSMS works when connecting to the remote server only the ADSI query will not work. I can go on my old machine connect remotely to the server and the query runs I can RDP to the server on my new or old machine and the query will run on the server itself it just will not run if I connect via SSMS from my new machine to the remote server. I have also tried other apps like TOAD and get same error.
February 14, 2024 at 10:14 pm
The only thing I can think of is user permissions. Likely something with permission chaining or delegating permissions (not sure if those terms are correct, but I think so). I am guessing that the permissions on the linked server ADSI are to run as the user executing the query (ie you) and SQL doesn't "trust" your computer and therefore cannot verify you are who you say you are.
Offhand I am not certain how to fix that as I never ended up getting it fixed myself (my IT department didn't allow me to have the SQL instances set up for delegation for whatever reason), so I had to hard-code in who the LDAP lookup account is (which was provided to me by my IT department).
If I had to guess, I'd say that your old machine was allowed to pass along the credentials from the local machine to the SQL instance to the linked server connection but your new one is not.
seems a lot of people resort to the solution I did as per bing:
https://www.bing.com/search?q=sql+server+Msg+7321%2C+Level+16%2C+State+2&PC=U316&FORM=CHROMN
Mind you, you may be able to work with your IT department to get delegation turned on for the server and possibly all machines in the chain so you can do the LDAP lookups, but I know my IT department was against it.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply