Host-based backup of VMware vSphere VMs.
pufferdude
Expert
Posts: 222
Liked: 15 times
Joined: Jul 02, 2009 8:26 pm
Full Name: Jim
Contact:

Can't get SureBackup SQL test to not fail

Post by 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
Product Manager
Posts: 20270
Liked: 2252 times
Joined: Oct 26, 2012 3:28 pm
Full Name: Vladimir Eremin
Contact:

Re: Can't get SureBackup SQL test to not fail

Post by veremin »

Can you write an error that SureBackup Job throws? Thanks.
foggy
Veeam Software
Posts: 21069
Liked: 2115 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: Can't get SuerBackup SQL test to not fail

Post by foggy » 1 person likes this post

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
Expert
Posts: 222
Liked: 15 times
Joined: Jul 02, 2009 8:26 pm
Full Name: Jim
Contact:

Re: Can't get SureBackup SQL test to not fail

Post by 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
Veeam Software
Posts: 21069
Liked: 2115 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: Can't get SureBackup SQL test to not fail

Post by 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
Service Provider
Posts: 1092
Liked: 134 times
Joined: May 14, 2013 8:35 pm
Full Name: Frank Iversen
Location: Norway
Contact:

[MERGED] Surebackup - sql verification failed

Post by 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
Enthusiast
Posts: 26
Liked: 11 times
Joined: May 01, 2014 9:48 am
Full Name: marc.rousseau
Contact:

Re: Surebackup - sql verification failed

Post by 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
Service Provider
Posts: 1092
Liked: 134 times
Joined: May 14, 2013 8:35 pm
Full Name: Frank Iversen
Location: Norway
Contact:

Re: Can't get SureBackup SQL test to not fail

Post by frankive »

+1
This is a "must have" for us as a service provider
frankive
Service Provider
Posts: 1092
Liked: 134 times
Joined: May 14, 2013 8:35 pm
Full Name: Frank Iversen
Location: Norway
Contact:

Re: Surebackup - sql verification failed

Post by frankive »

Ah, thanks!
A very nice post blog here as well:
http://www.danilochiavari.com/2014/12/0 ... eam-br-v8/
lennis40
Expert
Posts: 123
Liked: 3 times
Joined: Nov 11, 2014 11:03 pm
Full Name: Michael
Contact:

Re: Can't get SureBackup SQL test to not fail

Post by 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
Service Provider
Posts: 1092
Liked: 134 times
Joined: May 14, 2013 8:35 pm
Full Name: Frank Iversen
Location: Norway
Contact:

Re: Can't get SureBackup SQL test to not fail

Post by frankive » 1 person likes this post

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
Expert
Posts: 123
Liked: 3 times
Joined: Nov 11, 2014 11:03 pm
Full Name: Michael
Contact:

Re: Can't get SureBackup SQL test to not fail

Post by lennis40 »

frankive wrote: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
Service Provider
Posts: 1092
Liked: 134 times
Joined: May 14, 2013 8:35 pm
Full Name: Frank Iversen
Location: Norway
Contact:

Re: Can't get SureBackup SQL test to not fail

Post by 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
Expert
Posts: 123
Liked: 3 times
Joined: Nov 11, 2014 11:03 pm
Full Name: Michael
Contact:

Re: Can't get SureBackup SQL test to not fail

Post by lennis40 »

Thanks. I'll give it a shot.
lennis40
Expert
Posts: 123
Liked: 3 times
Joined: Nov 11, 2014 11:03 pm
Full Name: Michael
Contact:

Re: Can't get SureBackup SQL test to not fail

Post by 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
Novice
Posts: 6
Liked: never
Joined: Feb 12, 2013 11:19 am
Contact:

Re: Can't get SureBackup SQL test to not fail

Post by 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
Veeam Software
Posts: 21069
Liked: 2115 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: Can't get SureBackup SQL test to not fail

Post by foggy »

Please contact technical support for assistance with this.
cffit
Veteran
Posts: 338
Liked: 35 times
Joined: Jan 20, 2012 2:36 pm
Full Name: Christensen Farms
Contact:

Re: Can't get SureBackup SQL test to not fail

Post by 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
Veteran
Posts: 391
Liked: 107 times
Joined: Apr 27, 2015 1:59 pm
Full Name: Ryan Jacksland
Location: NY, USA
Contact:

Re: Can't get SureBackup SQL test to not fail

Post by 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!
VMCE v9
cffit
Veteran
Posts: 338
Liked: 35 times
Joined: Jan 20, 2012 2:36 pm
Full Name: Christensen Farms
Contact:

Re: Can't get SureBackup SQL test to not fail

Post by 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
Veteran
Posts: 391
Liked: 107 times
Joined: Apr 27, 2015 1:59 pm
Full Name: Ryan Jacksland
Location: NY, USA
Contact:

Re: Can't get SureBackup SQL test to not fail

Post by JaxIsland7575 »

Once I verified that SQL permissions were correct, I used the default SQL "check box" and added no manual arguments.
VMCE v9
cffit
Veteran
Posts: 338
Liked: 35 times
Joined: Jan 20, 2012 2:36 pm
Full Name: Christensen Farms
Contact:

Re: Can't get SureBackup SQL test to not fail

Post by 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
Veteran
Posts: 391
Liked: 107 times
Joined: Apr 27, 2015 1:59 pm
Full Name: Ryan Jacksland
Location: NY, USA
Contact:

Re: Can't get SureBackup SQL test to not fail

Post by 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!
VMCE v9
cffit
Veteran
Posts: 338
Liked: 35 times
Joined: Jan 20, 2012 2:36 pm
Full Name: Christensen Farms
Contact:

Re: Can't get SureBackup SQL test to not fail

Post by 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
Veeam Software
Posts: 21069
Liked: 2115 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: Can't get SureBackup SQL test to not fail

Post by foggy »

cffit wrote: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.
cffit wrote: 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
Service Provider
Posts: 24
Liked: 1 time
Joined: Aug 28, 2013 10:44 am
Full Name: Werner Steinegger
Contact:

[MERGED]: Feature Request: USer Credentials for Surebackup-J

Post by 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). :wink:

Best regards
Werner St.
foggy
Veeam Software
Posts: 21069
Liked: 2115 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: Can't get SureBackup SQL test to not fail

Post by 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
Expert
Posts: 121
Liked: 21 times
Joined: Feb 18, 2015 8:13 pm
Full Name: Randall Kender
Contact:

Re: Can't get SureBackup SQL test to not fail

Post by 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
Veeam Software
Posts: 21069
Liked: 2115 times
Joined: Jul 11, 2011 10:22 am
Full Name: Alexander Fogelson
Contact:

Re: Can't get SureBackup SQL test to not fail

Post by foggy »

Hi Randall, have you checked verification options in the SureBackup job?
bg.ranken
Expert
Posts: 121
Liked: 21 times
Joined: Feb 18, 2015 8:13 pm
Full Name: Randall Kender
Contact:

Re: Can't get SureBackup SQL test to not fail

Post by 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.
Post Reply

Who is online

Users browsing this forum: Google [Bot], nvdwansem and 69 guests