Hello

We have a single DPM 2010 Server instance (with Agent version 3.0.8195.0) and we are trying to protect databases on three SQL Server 2008 R2 x64 SP1 instances, which live on the same server.

The instances all listen on the same port, but on different IP addresses allocated to the same server. No instance listens on the default IP of the server. No instance listens on the localhost IP.

When enumerating the SQL data sources on the DPM server (e.g. when creating a new Protection Group), we can see the details for one of the three instances but the other two instances error out (after a very long time).

The errors shown for the two instances are:
For each Database: DPM could not enumerate the application component <SQL DB> on <PS> ID 964. Please mae sure that the writer is in good state.
For the instance: DPM could not connect to SQL Server instance <Instance> on the <PS> ID 966. Please make sure that SQL server instance is in good state.

I can confirm that:
1. Communication between DPM and the protected server seems fine
2. The SQL Server VSS writer is not in an error or inconsistent state (with "vssadmin list writers")
3. The "NT Authority\Local System" account has SysAdmin SQL permissions for all instances
4. The SQLServerVSSWriter service uses the "Local System" account
5. The backups for the instance that does work is fine and running stable
6. There are no known issues with the two instances. They are running, in production and we have not come across any issues when connecting to them in SSMS or otherwise

For the two instances that don't work, I have run the SQL Profiler when DPM was querying these instances. I can see the DPM Agent log on to the instance, retrieve the list of databases with:
select name,  recovery_model_desc, state_desc, CONVERT(integer, is_in_standby), ISNULL(source_database_id,0) from master.sys.databases

and query the databases with :
select rtrim(physical_name),rtrim(type_desc),rtrim(state_desc), is_name_reserved from sys.master_files where DB_ID(N'master') = database_id

But there is no futher communication.

Does anyone have any idea on why DPM can't enumerate the SQL databases on the two instances when the DPM Agent appears to be able to log on and have access to the SQL instance?

Thanks

Sunil

Hi Sunil,

On your SQL servers open the command prompt and run Diskshadow. 

type List Writers

See if the SQL Writer is enumerating your SQL instances in that output.

St

Hi Stephane,

Yes I can see all three instances and their databases listed in the command output e.g. (slightly obfuscated):

+ Component "SqlServerWriter:\LOBSTER\MSSQL2008R2\Plan"
        - Name: Plan
        - Logical path: LOBSTER\MSSQL2008R2
        - Full path: \LOBSTER\MSSQL2008R2\Plan
        - Caption:
        - Type: VSS_CT_FILEGROUP [2]
        - Is selectable: TRUE
        - Is top level: TRUE
        - Notify on backup complete: TRUE
        - Paths affected by this component:
                - D:\Data\MSSQL10_50.MSSQL2008R2\MSSQL\DATA
        - Volumes affected by this component:
                - \\?\Volume{8f2ecac0-c24b-4bb4-980f-0c85cd4fd3fe}\ [D:\]
        - Component Dependencies:   

Thanks

Sunil

Hi,

It's a strange behaviour...

By running DiskShadow List writers command, if SQL VSS is able to enumerate all your SQL components you should see them in DPM console.

Stephane

Hi Stephane,

Yes, it is strange behaviour - I don't know why one instance would be OK but the other two have trouble.

I don't know where I can look next to see where the problem might be.

Sunil

Hi,

Could you check DPMRACUrr.errlog in DPM\TEMP for any error message ?

St

Hi Stephane,

The log file is opaque, I don't know what I am looking for and there is too much noise. I've looked at the log after I tried enumerating the SQL instances in DPM (Modify Protection Group), but I only see these kinds of messages that specifically reference the protected server:

15A4    09B8    02/19    12:03:13.654    20    protocolhandshakehelper.cpp(452)    [000000000547CEF0]        NORMAL    Protocol selected for host [lobster.dmz.spiral] is [KERBEROS]

15A4    261C    02/19    12:03:13.628    18    fsutils.cpp(504)        021C4885-BFEB-4C9D-B54F-5F0FF24A1D91    WARNING    Failed: Hr: = [0x80070003] : Failed to get attributes for \\?\Volume{b16cad29-58f5-11e2-81b5-d4bed9a8720a}\5270b2cf-7b41-43b4-81df-55c5e1f131b3\Incremental\0ff8b8cb-dea8-4d01-8b4a-98ce201ce437\D-Vol\Data\Client\MSSQL10_50.MSSQL2008R2\MSSQL\DATA\DPM_SQL_PROTECT\LOBSTER\MSSQL2008R2\Plan_log.LDF\

Note the second error comes from the instance on the server that DPM can "see" and protect.

Thanks

Sunil

Hi Sunil,

You should use this command to made your search easiest :

Browse to Client Side Activity-- %Program Files%\Microsoft Data Protection Manager\DPM\Temp

find /I "YourStringSearch" dpmra*.errlog >C:\DPMFiltererror.txt

YourStringSearch could be "Access denied", etc.

I noticed that your server FQDN is [lobster.dmz.spiral]. Does your SQL Servers are hosted in DMZ ?

St

Hi Stephane,

Thanks for the tips - I searched for the "access denied" string in the log file that was being written when I was trying to enumerate the datasources with DPM and there are many errors that look like:

3E48    045C    02/19    11:32:10.421    31    sqlqueryhelper.cpp(202)    [00000000045F4EC8]        WARNING    Failed: Hr: = [0x80004005] SQL Instance - [LOBSTER\MSSQL2008R2] refusing connection
3E48    045C    02/19    11:32:10.421    31    sqlqueryhelper.cpp(203)            WARNING    Failed: Hr: = [0x80990f85] : F: lVal : ((HRESULT) (((unsigned long)(1)<<31) | ((unsigned long)(0x99)<<16) | ((unsigned long)(0xF80 + (0x05)))) )
3E48    045C    02/19    11:32:10.421    31    sqlqueryhelper.cpp(345)    [00000000045F4EC8]        WARNING    SQL - Error Description "[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied."
3E48    045C    02/19    11:32:10.421    31    sqlqueryhelper.cpp(358)    [00000000045F4EC8]        WARNING    Failed: Hr: = [0x80004005] SQL - sql code = 17 (Errorlevel - 0)
3E48    045C    02/19    11:32:10.421    31    sqlqueryhelper.cpp(366)    [00000000045F4EC8]        WARNING    SQL - Detailed Description "[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied."

But that SQL instance is a valid instance and I can connect to it using SQL Server Management Studio (and connect remotely). It also has the "NT Authority\System" configured as sysadmin. I'm going look look up what those error codes mean.

PS: The domain says "DMZ" but the DPM server and Protected server are in the same subnet - the naming is more historic than functional.

Thanks

Sunil

• Edited by Sunil-Spiral Tuesday, February 19, 2013 5:50 PM

Hi Stephane,

Thanks for the tips - I searched for the "access denied" string in the log file that was being written when I was trying to enumerate the datasources with DPM and there are many errors that look like:

3E48    045C    02/19    11:32:10.421    31    sqlqueryhelper.cpp(202)    [00000000045F4EC8]        WARNING    Failed: Hr: = [0x80004005] SQL Instance - [LOBSTER\MSSQL2008R2] refusing connection
3E48    045C    02/19    11:32:10.421    31    sqlqueryhelper.cpp(203)            WARNING    Failed: Hr: = [0x80990f85] : F: lVal : ((HRESULT) (((unsigned long)(1)<<31) | ((unsigned long)(0x99)<<16) | ((unsigned long)(0xF80 + (0x05)))) )
3E48    045C    02/19    11:32:10.421    31    sqlqueryhelper.cpp(345)    [00000000045F4EC8]        WARNING    SQL - Error Description "[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied."
3E48    045C    02/19    11:32:10.421    31    sqlqueryhelper.cpp(358)    [00000000045F4EC8]        WARNING    Failed: Hr: = [0x80004005] SQL - sql code = 17 (Errorlevel - 0)
3E48    045C    02/19    11:32:10.421    31    sqlqueryhelper.cpp(366)    [00000000045F4EC8]        WARNING    SQL - Detailed Description "[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied."

But that SQL instance is a valid instance and I can connect to it using SQL Server Management Studio (and connect remotely). It also has the "NT Authority\System" configured as sysadmin. I'm going look look up what those error codes mean.

PS: The domain says "DMZ" but the DPM server and Protected server are in the same subnet - the naming is more historic than functional.

Thanks

Sunil

• Edited by Sunil-Spiral Tuesday, February 19, 2013 5:50 PM

Hi Stephane,

Thanks for the tips - I searched for the "access denied" string in the log file that was being written when I was trying to enumerate the datasources with DPM and there are many errors that look like:

3E48    045C    02/19    11:32:10.421    31    sqlqueryhelper.cpp(202)    [00000000045F4EC8]        WARNING    Failed: Hr: = [0x80004005] SQL Instance - [LOBSTER\MSSQL2008R2] refusing connection
3E48    045C    02/19    11:32:10.421    31    sqlqueryhelper.cpp(203)            WARNING    Failed: Hr: = [0x80990f85] : F: lVal : ((HRESULT) (((unsigned long)(1)<<31) | ((unsigned long)(0x99)<<16) | ((unsigned long)(0xF80 + (0x05)))) )
3E48    045C    02/19    11:32:10.421    31    sqlqueryhelper.cpp(345)    [00000000045F4EC8]        WARNING    SQL - Error Description "[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied."
3E48    045C    02/19    11:32:10.421    31    sqlqueryhelper.cpp(358)    [00000000045F4EC8]        WARNING    Failed: Hr: = [0x80004005] SQL - sql code = 17 (Errorlevel - 0)
3E48    045C    02/19    11:32:10.421    31    sqlqueryhelper.cpp(366)    [00000000045F4EC8]        WARNING    SQL - Detailed Description "[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied."

But that SQL instance is a valid instance and I can connect to it using SQL Server Management Studio (and connect remotely). It also has the "NT Authority\System" configured as sysadmin. I'm going look look up what those error codes mean.

PS: The domain says "DMZ" but the DPM server and Protected server are in the same subnet - the naming is more historic than functional.

Thanks

Sunil

• Edited by Sunil-Spiral Tuesday, February 19, 2013 5:50 PM

Hi Stephane,

Thanks for the tips - I searched for the "access denied" string in the log file that was being written when I was trying to enumerate the datasources with DPM and there are many errors that look like:

3E48    045C    02/19    11:32:10.421    31    sqlqueryhelper.cpp(202)    [00000000045F4EC8]        WARNING    Failed: Hr: = [0x80004005] SQL Instance - [LOBSTER\MSSQL2008R2] refusing connection
3E48    045C    02/19    11:32:10.421    31    sqlqueryhelper.cpp(203)            WARNING    Failed: Hr: = [0x80990f85] : F: lVal : ((HRESULT) (((unsigned long)(1)<<31) | ((unsigned long)(0x99)<<16) | ((unsigned long)(0xF80 + (0x05)))) )
3E48    045C    02/19    11:32:10.421    31    sqlqueryhelper.cpp(345)    [00000000045F4EC8]        WARNING    SQL - Error Description "[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied."
3E48    045C    02/19    11:32:10.421    31    sqlqueryhelper.cpp(358)    [00000000045F4EC8]        WARNING    Failed: Hr: = [0x80004005] SQL - sql code = 17 (Errorlevel - 0)
3E48    045C    02/19    11:32:10.421    31    sqlqueryhelper.cpp(366)    [00000000045F4EC8]        WARNING    SQL - Detailed Description "[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied."

But that SQL instance is a valid instance and I can connect to it using SQL Server Management Studio (and connect remotely). It also has the "NT Authority\System" configured as sysadmin. I'm going look look up what those error codes mean.

PS: The domain says "DMZ" but the DPM server and Protected server are in the same subnet - the naming is more historic than functional.

Thanks

Sunil

• Edited by Sunil-Spiral Tuesday, February 19, 2013 5:50 PM

Hi Stephane,

Thanks for the tips - I searched for the "access denied" string in the log file that was being written when I was trying to enumerate the datasources with DPM and there are many errors that look like:

3E48    045C    02/19    11:32:10.421    31    sqlqueryhelper.cpp(202)    [00000000045F4EC8]        WARNING    Failed: Hr: = [0x80004005] SQL Instance - [LOBSTER\MSSQL2008R2] refusing connection
3E48    045C    02/19    11:32:10.421    31    sqlqueryhelper.cpp(203)            WARNING    Failed: Hr: = [0x80990f85] : F: lVal : ((HRESULT) (((unsigned long)(1)<<31) | ((unsigned long)(0x99)<<16) | ((unsigned long)(0xF80 + (0x05)))) )
3E48    045C    02/19    11:32:10.421    31    sqlqueryhelper.cpp(345)    [00000000045F4EC8]        WARNING    SQL - Error Description "[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied."
3E48    045C    02/19    11:32:10.421    31    sqlqueryhelper.cpp(358)    [00000000045F4EC8]        WARNING    Failed: Hr: = [0x80004005] SQL - sql code = 17 (Errorlevel - 0)
3E48    045C    02/19    11:32:10.421    31    sqlqueryhelper.cpp(366)    [00000000045F4EC8]        WARNING    SQL - Detailed Description "[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied."

But that SQL instance is a valid instance and I can connect to it using SQL Server Management Studio (and connect remotely). It also has the "NT Authority\System" configured as sysadmin. I'm going look look up what those error codes mean.

PS: The domain says "DMZ" but the DPM server and Protected server are in the same subnet - the naming is more historic than functional.

Thanks

Sunil

• Edited by Sunil-Spiral Tuesday, February 19, 2013 5:50 PM

PS: The domain says "DMZ" but the DPM server and Protected server are in the same subnet - the naming is more historic than functional.

Ok, so no Firewall between your SQL server and your DPM ?

Let us know the result of your investigations.

Stephane

Hi Stephane,

There are host firewalls and IPSec policies, but there are "holes" to allow communication between the PS and DPM.

Just copying from above the results I found in the client log file:

I searched for the "access denied" string in the log file that was being written when I was trying to enumerate the datasources with DPM and there are many errors that look like:

3E48    045C    02/19    11:32:10.421    31    sqlqueryhelper.cpp(202)    [00000000045F4EC8]        WARNING    Failed: Hr: = [0x80004005] SQL Instance - [LOBSTER\MSSQL2008R2] refusing connection
3E48    045C    02/19    11:32:10.421    31    sqlqueryhelper.cpp(203)            WARNING    Failed: Hr: = [0x80990f85] : F: lVal : ((HRESULT) (((unsigned long)(1)<<31) | ((unsigned long)(0x99)<<16) | ((unsigned long)(0xF80 + (0x05)))) )
3E48    045C    02/19    11:32:10.421    31    sqlqueryhelper.cpp(345)    [00000000045F4EC8]        WARNING    SQL - Error Description "[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied."
3E48    045C    02/19    11:32:10.421    31    sqlqueryhelper.cpp(358)    [00000000045F4EC8]        WARNING    Failed: Hr: = [0x80004005] SQL - sql code = 17 (Errorlevel - 0)
3E48    045C    02/19    11:32:10.421    31    sqlqueryhelper.cpp(366)    [00000000045F4EC8]        WARNING    SQL - Detailed Description "[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied."

But that SQL instance is a valid instance and I can connect to it using SQL Server Management Studio (and connect remotely). It also has the "NT Authority\System" configured as sysadmin. I'm going look look up what those error codes mean.

Thanks

Sunil

Hi Sunil,

If you have host firewall and IPSec between the PS and DPMserver, you should verify that all protocols used by DPM is allowed ?

Technet link : Configuring firewalls

You should pay particular attention to SMB protocol.

Stephane

Hi Stephane,

Thanks for the link, but please do not focus on the IPSec and firewall as I have already tested with them turned off and it made no difference.

The issue I see from the client error log is that the DPM Agent is trying to connect to the SQL instance using "LOBSTER\MSSQL2008R2" and fails. If I use this in UDL connection string or in SSMS, it succeeds.

I don't understantand why a UDL file can succeed where the DPM Agent fails when using the same instance name.

Thanks

Sunil

Hi Sunil,

Did you ever find a solution to this problem? I have the same problem with an SQL 2012 server with 2 instances and I can only properly backup only one.

Thanks in advance,

Nick

Hi,

Please disregard this post - it was meant for a different thread - my bad....

let me see if I can you with this issue...

We are talking for a simple SQL server (only one server that is) in a virtual machine. I have no other protection group for that server. I'm just trying to create a new protection group that contains only the SQL instances, so I'm not performing any kind of host-level backup.

What do you mean by "Secondary"?

Thanks in advance!

Nick

FYI, it seems that after granting the NT AUTHORITY\SYSTEM account "sysadmin" server role, the instance is displayed property upon creation of the protection group.

Thanks again!

Nick

Hi,

Im having the exact same issue on a very similar setup, were the sql instances have separate ips and do not respnd on the host ip. Im thinking that is the cause of the issue, as I cant see either db instance in DPM. I have also checked the other settings as Sunil did above. Can anyone confirm that the only way to fix these is to allow the host ip to have access to the sql db instances? I'd like to avoid that if at all possiale.

Thanks,

-Greg

Hi,

Our Setup:

2 x SQL 2014 on W2012R2 - Clustered.
Multible Instanzes with separate IP addresses.
Multible Always-On Availability Groups (Listeners with separate IP)

In the SQL configurator, I have enabled the host ip and the instance ip (active and enabled = yes) for the instance.

When we create a protection Group in DPM 2012 R2, expand the Availability Group, we see only the hourglass (for houres).

Are the TCP ports relevant/to consider?