February 11, 2009 at 1:50 pm
I thought for sure that I would need to have the servers/instances involved in my log shipping process logging into the domain, but alas, I have set up log shipping, with the destination server being in another town, using three SQL Servers that all have their Server and Agent services loggign on as the local system account.
I am baffled. I do not know why it is working successfully and I need to know, so I know whether I have uncovered a security flaw in SQL 2005, or a fluke, or what, and if it's going to break when one of the servers gets rebooted or something.
So here's the config:
ServerA, default instance: Source DB.
Network path to backup folder: \\ServerB\SQL01
Secondary Server instances and Databases:
Copy Files: \\ServerB\Backups\DBName
Restore transaction logs to Standby mode.
Monitor Server instance: ServerA\Develop
Security option: Impersonating the proxy account of the job.
Source and destination instances are 9.0.3042 and the Monitor instance is 9.0.1399.
This should not work without any of the servers in question having domain accounts, but it does, and I am not sure what I missed.
The only odd thing I have seen is that on ServerA, where the source and the Monitor instances are, the SQL Server Integration Services service is running under the "NT AUTHORITY\NetworkService" account.
Anyone know what I am missing?
Thanks,
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
February 12, 2009 at 1:21 pm
So the SQL Agent service and the SQL Server database service are running under "LocalSystem" or a local account? If the passwords are the same, it's possible that there is some passthrough. I wouldn't expect that with LocalSystem, but perhaps the rights are granted when you set up log shipping.
I'll look around a bit.
February 12, 2009 at 3:25 pm
Is this W2K3?
I heard from a friend that the machines will connect across as doman/computername$ and this should work. Seems wrong to me, but perhaps it's behavior by design. Bad design to me, but I can see this one getting through.
February 13, 2009 at 7:08 am
That's the screwy thing I can't figure out. All services on all boxes are running as the "Local System" account, with the exception of the source server, housing the Principal instance and the monitor instance, has "SQL Integration Services" running under the "Network Service" account.
I am sure the log shipping is working because I created a table in the principal DB yesterday and waited a few minutes, then I saw that table in the secondary DB. Same thing when I deleted the table.
I can't imagine that the principal and secondary servers have the same PW for the LocalSystem account, and use pass-through security.
I don't know how these files are making it past security.
Please see attached.
Thanks,
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
February 14, 2009 at 9:47 am
Check the share permissions and the permissions on the underlying folders ... it's possible that they aren't secured.
February 14, 2009 at 10:13 am
has the network authority account for the secondary server been granted rights to the share on the primary perhaps?
---------------------------------------------------------------------
February 16, 2009 at 7:49 am
ServerA, the principal, is dumping the backups to \\ServerB\Share, and the share rights are Everyone Full controll, and ACL is Admins Full Control, Creator Owner Special permissions, Everyone List Folder Contents, System Full control, and SystemB\Users Read and Execute.
With these rights, an account that is local to another box should not have access to this box.
ServerB is the secondary and houses the jobs which copy the backup to another location on the same server and restore the log backups.
Thanks,
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
February 16, 2009 at 11:02 am
This apparently is not specific to log shipping. I just ran this TSQL from ServerA:
Backup Database MDSS To Disk = '\\ServerB\Share\MDSS2.bak' With Init, Stats=20
Results:
Processed 352 pages for database 'MDSS', file 'mdss_data' on file 1.
100 percent processed.
Processed 1 pages for database 'MDSS', file 'mdss_log' on file 1.
BACKUP DATABASE successfully processed 353 pages in 4.988 seconds (0.579 MB/sec).
Now, when a SQL Server does not have network access, even though the person executing the query does, the query will normally fail because it executes under the context of the SQL Server runs under.
I don't know if it would have anything to do with it, but the "SQL Server Proxy Account" is disabled on this server.
I'm pulling the security guys in on this now.
Thanks,
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
February 16, 2009 at 1:23 pm
I have found something odd, that I believe is related to the log shipping. The secondary server is attempting, unsuccessfully, too log in to the Monitor SQL Server. I am not sure why this is, but it keeps trying, multiple times a second.
Date2/16/2009 3:15:09 PM
LogSQL Server (Current - 2/16/2009 3:15:00 PM)
SourceLogon
Message
Login failed for user 'DOMAIN\SERVERB$'. [CLIENT: 1.2.152.56]
Does this spark any ideas?
Thanks,
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
February 17, 2009 at 6:24 am
OK, now I wonder about the SID used for the System account. Can it be that the SID is the same for all installations of Win 2003, since it is an account that is default to all installations?
I realize this shouldn't be, beause even the MS docs on log shipping state that the SQL Services must run under accounts that have access to the network resources they need to use.
So far I have not been able to come up with any better ideas.
Thanks,
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
February 26, 2009 at 12:23 pm
I have it partially figured out. First, I found that the "Local System" account IS able to access network resources, in Win 2003, under the name Domain\ComputerName$. This is a change from previous versions of Windows, and counter-intuitive to what you would think, especially since there is also a "Network Service" account available in Win2003. The machine account, as DomainName\ComputerName$ is generally referred to, is a member of the special group "Authenticated Users" by default, and I don't think there is a way to that fact.
Next, I found that it is actually a Windows security issue, not a SQL Server issue. The behavior is that when on the ACL of the share where the backup is being written includes "Creator Owner" with full control, even though no actual rights are being granted to the machine account OR Authenticated Users, the machine account can write to the share.
Now I am trying to figure out why having Creator Owner with Full Control in the directory's ACL allows a machine account to write to a given directory across the network. I'm open to ideas.
Thanks,
Chris
Learning something new on every visit to SSC. Hoping to pass it on to someone else.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply