Brian,
It is not a firewall issue nor a broken SQL Server. This only affects SQL 2008 and higher instances running on Server 2008 and higher.
My opinion is that the problem is with Red Gate's reliance on SETUSER and SQL 2008 and higher on Windows Server 2008 and higher usage of Service SIDs.
From MSDN http://msdn.microsoft.com/en-us/library/ms143504.aspx:
SQL Server enables per-service SID for each of its services on Windows Server 2008 or Windows Vista operating systems in SQL Server 2008 R2 to provide service isolation and defense in depth. The per-service SID is derived from the service name and is unique to that service. For example, a service SID name for SQL Server service might be NT Service\MSSQL$<InstanceName>. Service isolation enables access to specific objects without the need to run a high-privilege account or weaken the security protection of the object. By using an access control entry that contains a service SID, a SQL Server service can restrict access to its resources.
In SQL 2005, if you used domain accounts for the SQL services the local groups COMPUTER\SQLServer2005MSSQLUser$ComputerName$MSSQLSERVER and COMPUTER\SQLServer2005SQLAgentUser$ComputerName$MSSQLSERVER
would get created and they would contain those domain service accounts. Those groups were granted Sysadmin rights on the SQL 2005 instance.
In SQL 2008 and higher, when running on Windows 2008 and higher, the situation changes. Those local groups are still created and granted Sysadmin rights, but if you look at the members of those local groups, you won't find the domain accounts listed as members. Instead you will find the Service SIDs created during setup, listed as 'NT SERVICE\MSSQLSERVER (SID GUID)'
This is actually a good thing! The SQL Agent account still has Sysadmin rights so there should not be a permissions issue... except if you use a deprecated function such as SETUSER which is not able to handle this scenario.
So IMHO it is a problem that Red Gate needs to address with their product. As other users here, I wasted a lot of time trying to figure out a permissions issue that didn't exist. For you to put the onus back on your customers comes across as arrogant and insulting. We know what we're doing. / comments
- Community
- SQL Backup Previous Versions
- Error 880 permission problem with scheduled backup
Brian,
It is not a firewall issue nor a broken SQL Server. This only affects SQL 2008 and higher instances running on Server 2008 and higher.
My opinion is that the problem is with Red Gate's relia...
0 votes
