User profile for jhboricua

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

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

So I got this working now. When examining the logs I saw the following on the failed backup jobs: "SQL Backup could not access [HKEY_LOCAL_MACHINESOFTWARERed GateSQL BackupBackupSettingsGlobal(LOCAL)] running as user [svcRedGate]." So I had to give the service account that runs the SQL Backup Agent account {svcRedGate] permissions to the registry keys at [HKEY_LOCAL_MACHINESOFTWARERed GateSQL BackupBackupSettingsGlobal(LOCAL)]. Then I tried to run a backup again and it failed with: "Warning 402: Failed to open local data store: Access to the database file is not allowed. [File name=D:Red GateSQL BackupData(local)data.sdf]" Apparently the service account didn't have permissions set here either, to Regdate's OWN FILES!!! Again, I gave the service account that runs the SQL Backup Agent service permissions to the Red Gate directories. After doing this, backups were successful. I can now also browse the network share for restores, which pretty much confirms to me this was NEVER a share permission issue but missing permissions on RedGate's end. I then asked the support technician I was working with why were this permissions missing in the first place? During the install I was asked both for the path where I wanted the tool installed and also under what account I wanted the Backup Agent service to run and I provided that info. So, why didn't the installer grant the account the permissions it NEEDED for the tool to work in the first place? This is the response I received: In regards to this situation, I definitely understand your frustration, and how challenging it was for you to properly set up the permissions to get the software running correctly. But ultimately, the way our tool handles permissions is a design decision. The majority of our customers prefer to have that granularity and control to configure permissions within their environment, for the sake of security (even though it is laborious). In a "locked down" environment where there are intentionally multiple layers of permissions for security reasons, it would be concerning if a 3rd party tool can easily and automatically bypass these various layers. At the end of the day, it is going to be hard for us to meet everyone's needs. As you can see from the forum posts, there are definitely users who feel the same way as you (regarding this particular issue), but we would suspect that there would be an even larger and more critical backlash if we were to have "an automated permission handling". I personally find the explanation to be absurd and, in my opinion, I see this as a design flaw, pure and simple. There's nothing about this that speaks security when the tool functionality is BROKEN unless I manually do the steps above. Back when we were doing SQL backups via Commvault, I didnâ€™t have to manually give the account that runs the Commvault service permissions to the Commvaultâ€™s directory so it could read its own files or to the Commvault registry keys so it could access its own settings. Commvault handled that itself when it was installed. All I had to make sure was that the service account had permissions to the final destination of the backup files. When doing SQL backups natively via SQL, I donâ€™t have to give the SQL Agent service account permissions to the directory where the SQL binaries are nor I have to give it permissions to the SQL registry keys. All those are handled by the installer when I specified the service accounts and paths. All I have to make sure, again, is that the service account has permissions to the final destination of the backup files. The ONLY time I've had to mess with an application in this manner has been because the application was either poorly designed or it's an ancient app that was not designed with modern Windows systems in mind. Which in both cases mean they didn't follow MS design best practices for applications, especially in regards to security. These are the apps that want to run under elevated rights (think local admin credentials) in order to work. So, in order mitigate risk and not have them run with elevated rights, we end up having to mess with manually assigning permissions to regkeys and directories/files. In my mind, there's no excuse for a modern application such as RedGate Backup Pro to require this level of meddling. /end rant / comments

So I got this working now. When examining the logs I saw the following on the failed backup jobs: "SQL Backup could not access [HKEY_LOCAL_MACHINESOFTWARERed GateSQL BackupBackupSettingsGlobal(LOCA...

0 votes

Crap, you're right. Since it was a new instance I had not setup the backup jobs yet because I first was trying to restore the database for the application we're trying to migrate. But I just looked at the job we setup for backing up the system databases, which also writes to the same network share and it is also failing. / comments

Crap, you're right. Since it was a new instance I had not setup the backup jobs yet because I first was trying to restore the database for the application we're trying to migrate. But I just looked...

0 votes

The referenced thread dates back to 2009 and there's still no fix yet from Red Gate? Seriously? / comments

The referenced thread dates back to 2009 and there's still no fix yet from Red Gate? Seriously?

0 votes

If I have to guess, you are probably having a different issue that has nothing to do with SQL Backup. The first thing you need to do is verify that you can connect to the SMTP server from the SQL box. Have you done this with say... Putty or the Telnet command connecting to port 25 on the SMTP server? http://technet.microsoft.com/en-us/libr ... v=exchg.65).aspx If the test fails, then you need to start your troubleshooting there and take SQL Backup out of the equation. It could be the Windows Firewall on either server blocking the connection, or a setting on the SMTP service not allowing relaying or a network device in the path not allowing the connection. / comments

If I have to guess, you are probably having a different issue that has nothing to do with SQL Backup. The first thing you need to do is verify that you can connect to the SMTP server from the SQL b...

0 votes

Hi Smiley, I while back I had to write a script to restore a bunch of databases. The backups were SQL Backup files. I blogged about it, you could use/adapt it to your needs. Hope it helps: http://sysadmingrunt.blogspot.com/2011/ ... ll_23.html / comments

Hi Smiley, I while back I had to write a script to restore a bunch of databases. The backups were SQL Backup files. I blogged about it, you could use/adapt it to your needs. Hope it helps:http://sy...

0 votes

If you're not squeamish about scripting, you could wrap everything into a powershell script that does: 1. Backup using Redgate SQL Backup. 2. Copy files after backup is complete. 3. Do the pruning of older files from the network share. I use a powershell script for #3 as I found that having the SQLBackup utility doing this was unreliable. / comments

If you're not squeamish about scripting, you could wrap everything into a powershell script that does: 1. Backup using Redgate SQL Backup. 2. Copy files after backup is complete. 3. Do the pruning ...

0 votes

Angela, I would strongly suggest that you add a disk dependency on the SQLBackupAgent generic cluster service in cluster administrator. See this thread for reference: http://www.red-gate.com/MessageBoard/vi ... hp?t=13361 When you install SQLBackup on a cluster the registry key: HKEY_LOCAL_MACHINE\SOFTWARE\Red Gate\SQL Backup\BackupSettingsGlobal\(LOCAL) is populated with the path of the shared cluster disk you specified during install. There is an issue where if the cluster service for SQLBackup tries to start before the disk resource is online (like during a failover or a reboot), the key will get reset with a local path on the node, effectively wiping the backup history and timeline from the SQLBackup GUI. By adding the disk dependency you'll ensure the service won't attempt to start before the shared disk becomes available. / comments

Angela, I would strongly suggest that you add a disk dependency on the SQLBackupAgent generic cluster service in cluster administrator. See this thread for reference:http://www.red-gate.com/Message...

0 votes

Hi Linda, Please confirm, does right clicking the clustered SQL server in the SQL Backup GUI deactivates the license on both the active AND passive node? / comments

Hi Linda, Please confirm, does right clicking the clustered SQL server in the SQL Backup GUI deactivates the license on both the active AND passive node?

0 votes

Did you already have native SQL backup jobs on the server. The history window will show non-RedGate SQL Backup jobs too. One way to know is to look at the very first column in the history window that has the SQL Backup logo. If its clear, then you are looking at regular sql backup job history, not RedGate SQL Backup jobs. / comments

Did you already have native SQL backup jobs on the server. The history window will show non-RedGate SQL Backup jobs too. One way to know is to look at the very first column in the history window th...

0 votes

How can we help you today?

jhboricua

Comments