August 14, 2013 at 4:35 am
Hi guys,
I have a central server and n other servers that are collected on using linked server connections.
My original setup (for testing) was:
Local login: my username
Remote user: a SQL Server login (if mixed mode)
For a login not defined in the list above: the same SQL Server login
Now I've been given a service account (no password expiry) and was planning to use only this. The steps I've done are:
1. Add the user to the central server in an Administrator group.
2. Granted this login sysadmin on the central server
3. Added the user to a "remote users" group on a remote server
4. Granted the login remote the same privileges I granted the SQL Server login (master/msdb read/exec)
I tested and I can log on to the central server with the service account, open mstsc & remote to the other server with that login & connect to the remote instance & run the queries I want; it's just not doing it with the Linked Server.
My new linked setup attempt is:
Local login: service account (in the admin group)
Remote user: I have tried both Impersonate & entering the DOMAIN\USER & password for the service account as the Remote User & password
For a login not defined in the list above: the same service account (domain\user & pass)
Anyone know why it's failing when my the user on the central server has sysadmin & I can remote to the other & run queries fine with mstsc? Thanks for any info
I found this but I'm not sure if it's relevent to me: http://qa.sqlservercentral.com/Forums/Topic1222568-146-1.aspx -- ideally looking to limit the dependency on other teams
August 14, 2013 at 4:58 am
your Linked server is working fine. test it and reply
August 14, 2013 at 5:05 am
With linked servers, the provided logins are always SQLUsers ! ( no windows domain accounts )
I don't use linked servers to gather inventory information from my sqlserver instances.
I use SQLAgent jobs that run their steps using a provided SQLAgent proxy account ( is windows domain account ~ service account ) which run Powershell scripts that collect / script and store the wanted information.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
August 14, 2013 at 5:08 am
Hi,
It is not working fine:
2013-08-14 12:05:00.97 Logon Login failed for user 'dom\svcusr'. Reason: Attempting to use an NT account name with SQL Server Authentication.
August 14, 2013 at 5:11 am
ALZDBA (8/14/2013)
With linked servers, the provided logins are always SQLUsers ! ( no windows domain accounts )I don't use linked servers to gather inventory information from my sqlserver instances.
I use SQLAgent jobs that run their steps using a provided SQLAgent proxy account ( is windows domain account ~ service account ) which run Powershell scripts that collect / script and store the wanted information.
Oh, that would explain it haha. I'm using SQL XE 🙁 I'll just try doing impersonate with the service account & my own (then try flipping the scheduled jobs to run as the service account).
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply