December 3, 2009 at 1:36 am
Firsty sorry about the lengthy senario but i'll try and summarise it 😀
I have 2 SQL 2008 boxes on Win Server 2008. No domain, basically 2 x free standing boxes.
- 'serverA'
- 'serverB'
I want to use trans replication using windows authentication for the agents linked servers etc but I'm not finding it as simple as it sounds.
My solution works...but..I've had to bastardise it with some SQL authentication. I was under the impression you could use windows only (?).
My questions are around the agent security and the linked servers that replication creates:
- Agents:
I'll use the the snapshot agent (pull) as an example:
this is configured to run under the local windows repl account on the subscriber end: 'serverB\Repl_acct'.
under the "connect to the publisher" section inthe publication: I entered the windows account from the publishing box: 'serverA\Repl_acct'.
but...it didn't seem to work.
Change this to a sql authenticated account and presto it works...why does it hate windows accounts?
(Both local accounts on the different boxes have the same username nd pwd.)
- Linked servers: I have been trying to make the windows account 'serverA\Repl_acct' map to 'serverB\Repl_acct' but it doesn't work (moans about untrusted domains). Once again SQL auth = in business.
I was under the impression both of these were thanks to pass through authentication (same windows username and pwd on each end bar the machine names)....have I misunderstood this?
I am wondering if this is something to do with Server 2008? (SP2)
Also...in this case what is everyones perception of how advantageous it is to use windows auth over SQL auth?
(consider I'm using 25+ pass phrases for all acoounts SQL and windows).
December 15, 2009 at 2:38 am
Have you been able to get the logins added to SQL Server successfully? I have tried the same scenario in the past with no luck by using local windows accounts across servers.
If you have an alert set up for severity 020 -- fatal error in current process, this alert will pop up because the credentials don't make it from windows to SQL Server.
Not really helping you out but confirming your findings:
1. configure an email alert for severity 020 (need an alert that fires a sql agent job)
2. Log on to serverA with Repl_acct
3. Open SSMS
4. Try to connect to serverB with windows authentication.
Provided there are no networking or DNS issues, a sev 20 will be raised and you can see the login failure as "login failed for user (null)" or "login failed for user '') -- something like that.
I think the problem is that serverB has no way to look up the account and ensure that the password is correct for the user that is logged on to serverA. When the systems are on a domain, SQL running under a domain acct will authenticate with active directory to ensure the identity of the user connecting is valid.
When it works with SQL Server authentication, it is because all of the identifying is occurring within SQL Server, disregarding Windows.
I would be curious to see if anyone else can comment and provide an alternate explanation or find a way to make local windows accounts accessible in SQL Server from other servers.
Regards,
Steve
December 15, 2009 at 1:43 pm
Hi Steve,
Yes you are 100% correct.
SQL authentication works as it is encapsulated within SQL (username + password = bingo).
But the windows accounts cannot authenticate due to nothing being able to say who they are or what their password is (apart from the host machines they reside on).
I've heard that this is possible (mind you, I also heard it is possible for a fat old man in a red suit to ride a sleigh of raindeer through the sky)...but it appears to me that due to there being nothing in the middle to help pass information around: Integrated security is left high and dry.
I would be keen on anyone elses experience trying to make raindeer tow a fat man around the world 😀
Carlton...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply