Can't get SureBackup SQL test to not fail

[pufferdude]

I have what should be a pretty simple setup: Two SQL/web servers in a surebackup job (one 2008 R2, the other 2012 R2) that rely on a single 2012 DC in the Application Group. The DC starts up just fine and passes all its tests. The other two hosts start up fine and pass the web server test but neither passes the SQL test. I'm not sure where to even start looking. Is there a "trick" to getting the surebackup job configured to accurately test a SQL server? I'm not even sure how to manually fire up the surebackup job so I can look inside the VMs to see what's happening. That said, IF I manually "instant restore" one of these VMs from backup, I can see that SQL server is running just fine... so this seems to be a "false negative" with the surebackup test... I must have something configured wrong.

Any advice on configuring or troubleshooting this thing?

[veremin]

Can you write an error that SureBackup Job throws? Thanks.

[foggy]

Jim, are you on v8? Starting v8, SQL test script does not simply ping the corresponding ports on the server, but instead checks connection to every instance, so requires appropriate rights to perform this (minimum rights are required, need to check the exact role). You can either give the service account rights on the SQL server or pass the credentials as script arguments in SureBackup job (check Veeam.Backup.SqlChecker.vbs in the product installation folder for details).

If you need assistance with this, feel free to contact technical support.

[pufferdude]

Foggy... thanks, I think this might be my issue. Currently the job is using the predefined SQL Server script. I don't see a way to edit the arguments when "Use Predefined test script" is chosen, so I guess I need to choose "Use the following test script" and then navigate to the SQL server script and fill out everything manually? But if that's the case and this script *cannot* work without credentials being passed via arguments, that makes me wonder why the option to use the predefined SQL Server script exists at all, since it would never be able to work.

I'm probably still missing something, but at least I can try to configure the SQL Server script manually and see what happens.

[foggy]

Well, it may work, if the service account has corresponding permissions. However, I agree this is not intuitive and we are going to add the ability to supply credentials via UI further down the road.

[frankive]

I have a surebackup I am testing out for a sql-server. If I enable sql script for verifying it fails with error code 2.
At the same time, I can fine telnet port 1433 from the veeam server against the masqerda ip of the sql-server.
What does the script relaly check? Is it not just a plain telnet check?

[marc.rousseau]

http://forums.veeam.com/vmware-vsphere- ... 26053.html
"Starting v8, SQL test script does not simply ping the corresponding ports on the server, but instead checks connection to every instance, so requires appropriate rights to perform this (minimum rights are required, need to check the exact role)."

[frankive]

+1
This is a "must have" for us as a service provider

[frankive]

Ah, thanks!
A very nice post blog here as well:
http://www.danilochiavari.com/2014/12/0 ... eam-br-v8/

[lennis40]

Where at in the SQLChecker.vbs script do you modify the username and password to use during the SureBackup job. I've had a support case open for serveral days, and everything I've been told to try hasn't worked. Still getting error code 2. I don't know why credentials aren't allowed to be entered when setting up the SureBackup job, seems like an oversight.

Support Case 00941222

[frankive]

I pass the username and password as an argument in the application group and under Test Scripts.
Take at look half way down this blog: http://www.danilochiavari.com/2014/12/0 ... eam-br-v8/

[lennis40]

I pass the username and password as an argument in the application group and under Test Scripts.
Take at look half way down this blog: http://www.danilochiavari.com/2014/12/0 ... eam-br-v8/

I've looked at that post, but do I just need username/password in the arguments section, or do I actually have to modify the script I am pointing to as well? That's what I don't understand. If it's just adding the username and password to the arguments section, that seems easy enough. Thanks.

[frankive]

you add username and password into the arguments, not the script itself. I never touch the script when I setup this.

I create a user named f.ex "surebackup" and with a password in the sql server you want to backup (and test). Just give it the lowest privilegie (public) like he says in the blog.it it that account you connect to when you pass the arguments.

[lennis40]

Thanks. I'll give it a shot.

[lennis40]

I have passed credentials via argument, but the SureBackup job is still failing on the SQL script. When I run the script from the command line, using the credentials created with Public permissions, it runs successfully on each SQL server I test. Working with support this afternoon. I have upped the application timeout to 900, as recommended by support, but still not having any luck.

[Geniek73]

I have the same problem with testing my SQL server using scritp. I always get en error code 2 - Unable to connect to SQL server.
I'm passing login credentials using customized script option as mentioned a few posts above. My arguments are: %log_path% %vm_ip% login pass
Does anyone got it working?

[foggy]

Please contact technical support for assistance with this.

[cffit]

I'm following this with some of the same questions.

When I select the SQL server role checkbox for the server, does that run the Veeam.BackupSqlChecker.vbs file during SB? If the answer is yes, then should giving the Veeam service account public permissions to all the databases work?

When I manually set the Veeam.Backup.Sqlchecker.vbs file to run, and add the argument %log_path% %vm_ip%, where do the log files get saved? When I specify the log file to go to c:\logs, it doesn't create any files.

I love this feature, but it is a little difficult to get going.

[JaxIsland7575]

Christensen,
I had an issue with my SureBackup and SQL jobs and what I did was start up a SureBackup job, then logged into the server and used SQL Management Studio to run the .vbs file manually. That kicked out an access denied so I went into the database and added the user I was running Veeam under to each DB.I mapped them to each instance with public permissions. After that I retried the script and it work.

Cheers!

[cffit]

Yes, I have removed the SQL role check from the SB job, then booted the lab and I was able to run the script from a command prompt. It worked fine.

Did you just check the SQL role box and that's it? Or did you manually point to the vbs file and add arguments to it?

[JaxIsland7575]

Once I verified that SQL permissions were correct, I used the default SQL "check box" and added no manual arguments.

[cffit]

Thanks for the info Jax

After testing all day I have found that even if you run the script under a user context that has public access to all the databases it will still fail unless you either specify the instance (even if there is only one default instance you still have to specify mssqlserver) or you add the veeam backup service account to the local administrator group on the SQL server. The script uses the "sc" command to query the instance on the SQL server, and you need to be a local administrator on the SQL server to be able to run that. If you specify the instance, you avoid the script having the use "sc" to do a query and can get by without adding the veeam backup account to the local administrator group.

So my questions for Veeam moderators are:

Does checking the SQL role for a VM in an application group basically just run the Veeam.Backup.SqlChecker.vbs file? If not, what does it do? If so, what location does %log_path% reference?

[JaxIsland7575]

I did not have to add the Veeam user onto the local server, but I failed to mention this was SQL 2005 on a Win 2003 server. I am sure with the changes in how servers / SQL handle permissions in newer versions you will see some variation from my experience!

[cffit]

This was SQL 2008 R2, so I'm not sure if that matters maybe. I could be mistaken, but I did a lot of testing and this was what I found. It would not work unless I specified the instance, and I only had the default instance on the SQL server. I tried running the sc commands on the Veeam Backup server under the veeam backup account context and it would get access denied error unless that account was a local admin on the SQL server. Because of that alone, the script would instantly fail.

[foggy]

Does checking the SQL role for a VM in an application group basically just run the Veeam.Backup.SqlChecker.vbs file? If not, what does it do?

Sets some timeouts to a more extended values.

If so, what location does %log_path% reference?

By default, it is something like: %programdata%\Veeam\Backup\<name of the job>\<VM name>_SQLChecker.log (the exact file name could be different).

[westCPS]

Hello!

As we opened a Support case (01130515) in the cp of veeam, because of not working sure backup Jobs (SQL & Exchange). We found out that Veeam use for the build-in SQL-Surebackup-Job the account of the veeam backup Service. So the error with the SQL-Surebackup solved by changing the Veeam Backup Service from local Service to a Domainaccount with Access-rights on the SQL-Server. In veeam gui it was not possible to Setup the build-in script with other credentials.


It would be nice for future use of Surebackup, to use the GUI to Setup the Credentials (same method as in the Backup-Jobs).

Best regards
Werner St.

[foggy]

Werner, the ability to specify credentials for the script will be available in the upcoming v9. Currently you can pass the credentials as script arguments, please see above for details.

[bg.ranken]

Hi foggy,

I've checked around v9 and I can't see a way to pass credentials to the script. Was this feature not implemented?

[foggy]

Hi Randall, have you checked verification options in the SureBackup job?

[bg.ranken]

Ah, I see that. I guess I was hoping that you meant passing one of the credentials stored in the credential manager, something similar to selecting the Guest OS credentials in a regular job. Even if there was just a drop down to select the credentials and then you could use %username% %password% in the argument line.

I guess I'm just not a huge fan of typing an actual password in the argument line. Is there any chance this might be something that I could request get added in for a future version, just for security reasons? For SQL it's not too big of a deal since the permissions you give on the SQL server are so low, but I have another script I have been working on where I need to supply domain credentials since my Veeam server is off the domain. I know it's still not 100% secure since someone could edit the script to get the actual password however in my case we limit who has access to the physical Veeam server, but more people have access to the console and I don't want those people to be able to see the credentials I'm using.