Evaluating Policies against SQL Server in DMZ

Aug 15, 2012 at 5:11 AM

Hello,

I'm trying to connect to the SQL Server which is in a different domain. The only way I can connect is to use SQL Authentication. How do I force Invoke-PolicyEvaluation to use SQL Authentication please? It seems that -TargetServerName can only work using Windows Authentication. I tried to pass connection string like "Server="+ $ServerName + ";UserID=user;Password=pwd", but got error:

<DMF:Exception type="string">Microsoft.SqlServer.Management.Dmf.PolicyEvaluationException: Exception encountered while executing policy 'Guest Permissions'. ---> Microsoft.SqlServer.Management.Common.ConnectionFailureException: Failed to connect to server . ---> System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

This info http://msdn.microsoft.com/en-us/library/cc645987.aspx doesn't help much :(

Any ideas?

Thanks

 

Developer
Aug 31, 2012 at 5:16 PM

Hello,

The limitation on your issue is not in the EPM framework but with the SQL Central Management Server (CMS).  It is documented that the CMS only supports Windows authentication: (http://msdn.microsoft.com/en-us/library/bb964743.aspx)

Central Management Servers and subordinate servers can be registered by using only Windows Authentication. Servers in local server groups can be registered by using Windows Authentication or SQL Server Authentication.

You could try setting up domain trusts to permit cross domain Windows authentication or possibly configure a VLAN to allow for the authentication to the DMZ. (http://technet.microsoft.com/en-us/library/cc773178(v=WS.10).aspx)

Hope this helps and good luck

Aug 9, 2013 at 12:16 PM
Hello,

I'm currently working on this feature. The idea is a gateway server which can access both the MS SQL Servers in the DMZ and also the central policy server. On this gateway you could run a slightly modified version of EPM_EnterpriseEvaluation_3.0.0.ps1. This script has his own server selection and also sql authentication.

I can't really see the dependency to the SQL CMS - it's only used for storing a list of servers which should be checked, or not?
Dec 12, 2013 at 1:35 PM
It might be possible to talk to your networking team and discuss the possibility to have a one-way trust from the Domain where the CMS is located to the DMZ. That is how I've seen many IT infrastructures with DMZs. With such a trust, any connection that originates from the CMS Domain is trusted and allowed to connect to the DMZ. However, any connection that originates from the DMZ cannot access the Domain the CMS is on. Just an idea on how to get around it. :) Hope it helps!