Sql Server restore from non-domain-joined server

[KeiichiKun]

Hi all,
I need to restore a sql server database from a backup copy; the sql server target is in our domain, but the server that contains backup copy is non-domain-joined, has only local users.
When I try to restore sql database I got an error "Specified user does not have sufficient permissions on remote host"
Obviously is correct, because this user does not have privileges on sql server machine, but I don't know how make this restore working.
Thanks for your help

[wishr]

Hi KeiichiKun,

Please make sure the account that you specify when connecting to the target SQL server has required permissions.

Thanks

[KeiichiKun]

It doesn't as I said before, because sql server seems not allowing give permission to a non domain or non local user.
The solution was creating a dedicated user on backup server and on sql server vm with the same password, and give to the local account of sql server the required permissions.
The user is not "the same" but has same name and same password, so veeam can procede.

[wishr]

You should just specify an account having the aforementioned permissions, when restoring a database.

[KeiichiKun]

I don't get the point.
In the first window of your link, I've given a username that has privileges to restore database (domain user) and that's correct.
In the second window:
- giving a user that can restore database (but cannot access backup machine, because is non-domain-joined) --> error
- giving a local user of the non-domain-joined machine (but cannot access the sql machine) --> error

I can't add to Sql Server users a local user of the non-domain-joined machine, because sql server seems not accept workgroup account.

The only solution I've found is "create a dedicated user on backup server and on sql server vm with the same password, and give to the local account of sql server the required permissions"

[wishr]

You should enter the credentials only once on the "Specify target SQL Server connection parameters" step of the Veeam Explorer for Microsoft SQL Server restore wizard. This account must have the permissions mentioned in the article I linked above: it must be any user that is a member of the "Local Administrator" group on the SQL server machine and have a "sysadmin" role assigned in the SQL server instance. In your case, it can be a domain account since the SQL server is domain-joined. This account does not require access to the backup server and repository where the backup files reside. It is just used for connecting to the SQL server and for performing the DB restore operations on it. If a Windows Security authentication window pops up after you completed the steps of the restore wizard you should just enter the same account from above. That is it.

Not sure what you meant when saying "first window" and "second window".

In case all the conditions from the above are met and you are still not able to perform the restore, I would suggest raising a support case with our support engineers since troubleshooting through the forum is not possible and is purely handled by our technical support team members.

I hope it helps. Thanks.

[KeiichiKun]

It doesn't work, but if I try to restore from a different backup repository (cifs from domain joined server) everything works fine.

I have entered the correct account in window "SPECIFY TARGET SQL SERVER CONNECTION PARAMETERS".

Then a window "SPECIFY TARGET SERVER CONNECTION CREDENTIALS" appears
- if I enter a valid domain account with full permission on sql server I got the error "Access is denied: unable to pass current account's context to the mount server aaa.aaa.aaa.aaa, please supply credentials and try again.", where aaa.aaa.aaa.aaa is the repository ip. I think it's a real error, because this user cannot have (in no way, it's not a local account of sql server vm and it's not a domain account, it's just a local account for repository) permission on sql server.
- if I enter a valid account for the repository (the same account that I've used to mount the server in Veeam), I got the error "Specified user does not have sufficient permissions on remote host"

I'll open a ticket.

[Andreas Neufert]

Likely you do not have a permission issue.
Please check if all needed Network ports are open and potentially windows firewalls are configuerd correctly for same communication:

SQL Restore (standard)
Console=>SQL VMs TCP445, TCP1433, UDP1434 (depend on SQL configuration), TCP1025-1034
Mount=>SQL VMs TCP6160, TCP445, TCP135, RPCHigh Ports TCP 49152 to 65535
SQL VMs=>Mount TCP3260

As well please make sure that the SQL network configuration allow the actual connection from outside of the server.
For example if you install SQL express the default configuration do not allow the connection from outside of the server.

[KeiichiKun]

If I use the method written above (creation of a user with same name and password on sql server and on repository server) everything works fine, so I don't understand how there could be some communication problem..
As I specified in first post, I'm trying to restore from a "backup copy" but It was more correct saying "backup copy from a backup copy job", but I don't think it makes any difference during restore..

[Andreas Neufert]

I still think that you see some side effects from communication issues. We have several fallback logics that allow us to fallback to other servers processing and existing used credentials.

[Andreas Neufert]

Let´s open a support case and upload needed logs. It will show what is going on. Remember that this is not a support forum.

[YouGotServered]

Hello, was there ever a resolution to this? I encountered a similar issue before. My backup server is not on the domain. I tried to restore directly to SQL and provided a domain admin account with full access to the VM and also the sysadmin role in SQL and kept getting hit with access denied errors. Eventually, I just restored the DB files locally to the backup server and transferred them to SQL via SMB.

We may have different issues, but I'd still be interested to hear what the solution was (besides the workaround listed above).

[Andreas Neufert]

B&R Server do not have to be joint to any domain. Usually it is an access/communication issue. Network ports not accessible on console/mount server (see above) or the SQL Server connections are not configured in a way that you can access the server from external (typically for SQL express default configuration).

[Andreas Neufert]

Best way to address this is to open a support ticket and let our support team have a look.

[KeiichiKun]

Case #04340585

[KeiichiKun]

Issue (that wasn't an issue at all..) has been fixed by the support.
I must specify, as mount server of repository, a server that can resolve domain users.
So we have changed the mount server from out-of-domain server to veeam backup server that is domain-joined and everything works fine.
Thanks

[Andreas Neufert]

This is strange as it works on my side without domain joining. Was the mount server not part of DNS name resolution and now it is ?

[KeiichiKun]

Out-of-domain mount server was specified using IP; anyway, if somewhere name was used, it's resolved by DNS

[Andreas Neufert]

Can you maybe check what connections are allowed on the SQL connection wizard, then I could check in my lab with same config?

[KeiichiKun]

Are you talking about network configuration in Sql Server Configuration Manager?
In this case, we have enable Shared Memory, Named Pipes and TCP/IP

[Andreas Neufert]

yes, thanks

[Andreas Neufert]

Do you have enabled in your domain any special policies arround Kerberos only authentication (Disabling NTLM v2)?

[KeiichiKun]

No
Thanks

[Andreas Neufert]

I have a small update here.
It looks like the security arround windows and SQL leads to the demand that the Veeam Explorer for SQL system needs to be part of the domain when the SQL server is part of a domain.
If you have a stand allone SQL server you can communicate without domain and Veeam Explorer for SQL would work.

We will documenting this better in our documentation which will lead into Veeam QA double checking our results.

[YouGotServered]

Good to know, thanks so much for the update. Would be good to have fully documented exact which pieces must be domain joined and which ones do not need to be.

[YouGotServered]

Hello! Was this ever documented? I have to do another SQL restore, so it made me wonder if a document was ever produced. Thank you!

[Kevil-LTU]

I have a small update here.
It looks like the security arround windows and SQL leads to the demand that the Veeam Explorer for SQL system needs to be part of the domain when the SQL server is part of a domain.
If you have a stand allone SQL server you can communicate without domain and Veeam Explorer for SQL would work.

We will documenting this better in our documentation which will lead into Veeam QA double checking our results.

This feels like a fairly major omission in the documentation for moving veeam off domain. I am trying to do a DB restore now (the first one after moving our veeam to a new domain and can no longer action the restore, i get the "Specified user does not have sufficient permissions" error even when using a domain admin account.

What is the workaround for this?

[PetrM]

Hi Kevin,

We don't have any special requirements for domain location of Veeam or SQL Server, but you should check the required permissions for the account being used for restore, in particular, that the account is a member of the local Administrators group on the target Microsoft SQL Server. If all necessary permissions are assigned to the account, please open a support case and paste its ID over here for our reference.

Thanks!