private void AddConnectionStringsToWrapperFromPolicy(AuditingPolicy wrapper, DatabaseSecurityPolicyProperties properties)
{
wrapper.ConnectionStrings.AdoNetConnectionString = properties.AdoNetConnectionString;
wrapper.ConnectionStrings.OdbcConnectionString = properties.OdbcConnectionString;
wrapper.ConnectionStrings.JdbcConnectionString = properties.JdbcConnectionString;
wrapper.ConnectionStrings.PhpConnectionString = properties.PhpConnectionString;
}
/// <summary>
/// Initializes a new instance of the
/// DatabaseSecurityPolicyUpdateParameters class with required
/// arguments.
/// </summary>
public DatabaseSecurityPolicyUpdateParameters(DatabaseSecurityPolicyProperties properties)
: this()
{
if (properties == null)
{
throw new ArgumentNullException("properties");
}
this.Properties = properties;
}
/// <summary>
/// Initializes a new instance of the
/// DatabaseSecurityPolicyUpdateParameters class with required
/// arguments.
/// </summary>
public DatabaseSecurityPolicyUpdateParameters(DatabaseSecurityPolicyProperties properties)
: this()
{
if (properties == null)
{
throw new ArgumentNullException("properties");
}
this.Properties = properties;
}
private void AddEventTypesToWrapperFromPolicy(AuditingPolicy wrapper, DatabaseSecurityPolicyProperties properties)
{
HashSet<string> events = new HashSet<string>();
if (properties.IsEventTypeDataAccessEnabled) events.Add(Constants.Access);
if (properties.IsEventTypeDataChangesEnabled) events.Add(Constants.Data);
if (properties.IsEventTypeSchemaChangeEnabled) events.Add(Constants.Schema);
if (properties.IsEventTypeGrantRevokePermissionsEnabled) events.Add(Constants.RevokePermissions);
if (properties.IsEventTypeSecurityExceptionsEnabled) events.Add(Constants.Security);
wrapper.EventType = events.ToArray();
}
private AuditingPolicy WrapPolicy(DatabaseSecurityPolicy policy)
{
AuditingPolicy wrapper = new AuditingPolicy();
DatabaseSecurityPolicyProperties properties = policy.Properties;
wrapper.UseServerDefault = properties.UseServerDefault;
wrapper.IsEnabled = properties.IsAuditingEnabled;
wrapper.StorageAccountName = properties.StorageAccountName;
AddEventTypesToWrapperFromPolicy(wrapper, properties);
this.FetchedProperties = properties;
return wrapper;
}
private void addStorageInfoToWrapperFromPolicy(AuditingPolicy wrapper, DatabaseSecurityPolicyProperties properties)
{
wrapper.StorageAccountName = properties.StorageAccountName;
if (properties.StorageAccountKey != null)
wrapper.StorageKeyType = Constants.StorageKeyTypes.Primary; // TODO - until we have in prodcution the secondary field - handle as alway primary
if (properties.SecondaryStorageAccountKey != null)
wrapper.StorageKeyType = Constants.StorageKeyTypes.Secondary;
}
private AuditingPolicy WrapPolicy(DatabaseSecurityPolicy policy)
{
AuditingPolicy wrapper = new AuditingPolicy();
DatabaseSecurityPolicyProperties properties = policy.Properties;
wrapper.UseServerDefault = properties.UseServerDefault;
wrapper.IsEnabled = properties.IsAuditingEnabled;
wrapper.DirectAccessEnabled = !properties.IsBlockDirectAccessEnabled;
addStorageInfoToWrapperFromPolicy(wrapper, properties);
AddEventTypesToWrapperFromPolicy(wrapper, properties);
this.FetchedProperties = properties;
return wrapper;
}
/// <summary>
/// Updates the storage properties of the policy that this object operates on
/// </summary>
private void UpdateStorage(AuditingPolicy policy, DatabaseSecurityPolicyProperties properties)
{
string storageAccountName = policy.StorageAccountName;
if (storageAccountName != null)
properties.StorageAccountName = storageAccountName;
if (string.IsNullOrEmpty(properties.StorageAccountName) && (!IgnoreStorage)) // can happen if the user didn't provide account name for a policy that lacked it
{
throw new Exception(string.Format(Microsoft.Azure.Commands.Sql.Properties.Resources.NoStorageAccountWhenConfiguringAuditingPolicy));
}
// no need to do time consuming http inteaction to fetch these properties if the storage account was not changed
if (properties.StorageAccountName == this.FetchedProperties.StorageAccountName)
{
properties.StorageAccountResourceGroupName = this.FetchedProperties.StorageAccountResourceGroupName;
properties.StorageAccountSubscriptionId = this.FetchedProperties.StorageAccountSubscriptionId;
properties.StorageTableEndpoint = this.FetchedProperties.StorageTableEndpoint;
}
else
{
properties.StorageAccountSubscriptionId = Subscription.Id.ToString();
properties.StorageAccountResourceGroupName = Communicator.GetStorageResourceGroup(properties.StorageAccountName);
properties.StorageTableEndpoint = Communicator.GetStorageTableEndpoint(properties.StorageAccountName);
}
if (!IgnoreStorage)
{
// storage keys are not sent when fetching the policy, so if they are needed, they should be fetched
Dictionary<Constants.StorageKeyTypes, string> keys = Communicator.GetStorageKeys(properties.StorageAccountResourceGroupName, properties.StorageAccountName);
if (policy.StorageKeyType == Constants.StorageKeyTypes.Primary)
properties.StorageAccountKey = keys[Constants.StorageKeyTypes.Primary];
else
properties.SecondaryStorageAccountKey = keys[Constants.StorageKeyTypes.Secondary];
}
}
/// <summary>
/// Updates the storage properties of the policy that this object operates on
/// </summary>
private void UpdateEventTypes(AuditingPolicy wrappedPolicy, DatabaseSecurityPolicyProperties properties)
{
string[] userEnteredEventType = wrappedPolicy.EventType;
if (userEnteredEventType == null || userEnteredEventType.Length == 0)
return;
HashSet<String> eventTypes = new HashSet<String>(userEnteredEventType);
if (!ValidateShortcutUsage(eventTypes, Constants.All))
throw new Exception(string.Format(Microsoft.Azure.Commands.Sql.Properties.Resources.InvalidEventTypeSet, Constants.All));
if (!ValidateShortcutUsage(eventTypes, Constants.None))
throw new Exception(string.Format(Microsoft.Azure.Commands.Sql.Properties.Resources.InvalidEventTypeSet, Constants.None));
properties.IsEventTypeDataAccessEnabled = ValueOfProperty(eventTypes, Constants.Access);
properties.IsEventTypeSchemaChangeEnabled = ValueOfProperty(eventTypes, Constants.Schema);
properties.IsEventTypeDataChangesEnabled = ValueOfProperty(eventTypes, Constants.Data);
properties.IsEventTypeSecurityExceptionsEnabled = ValueOfProperty(eventTypes, Constants.Security);
properties.IsEventTypeGrantRevokePermissionsEnabled = ValueOfProperty(eventTypes, Constants.RevokePermissions);
// we need to re-add the event types to the AuditingPolicy object to replace the All / None with the real values
if (userEnteredEventType.Contains(Constants.All) || userEnteredEventType.Contains(Constants.None))
AddEventTypesToWrapperFromPolicy(wrappedPolicy, properties);
}
/// <summary>
/// Unwrap the cmdlets model object and transform it to the communication model object
/// </summary>
/// <param name="policy">The AuditingPolicy object</param>
/// <returns>The communication model object</returns>
private DatabaseSecurityPolicyUpdateParameters UnwrapPolicy(AuditingPolicy policy)
{
DatabaseSecurityPolicyUpdateParameters updateParameters = new DatabaseSecurityPolicyUpdateParameters();
DatabaseSecurityPolicyProperties properties = new DatabaseSecurityPolicyProperties();
updateParameters.Properties = properties;
properties.RetentionDays = 90;
properties.IsAuditingEnabled = policy.IsEnabled;
properties.UseServerDefault = policy.UseServerDefault;
properties.IsBlockDirectAccessEnabled = !policy.DirectAccessEnabled;
UpdateEventTypes(policy, properties);
UpdateStorage(policy, properties);
return updateParameters;
}
private string ConstructAdoNetConnectionString(AuditingPolicy wrapper,DatabaseSecurityPolicyProperties properties)
{
string enterUser = Microsoft.Azure.Commands.Sql.Properties.Resources.EnterUserId;
string enterPassword = Microsoft.Azure.Commands.Sql.Properties.Resources.EnterPassword;
return string.Format("Server=tcp:{0},{1};Database={2};User ID={3}@{4};Password={5};Trusted_Connection=False;Encrypt=True;Connection Timeout=30",
properties.ProxyDnsName, properties.ProxyPort, wrapper.DatabaseName, enterUser, wrapper.ServerName, enterPassword);
}
private string ConstructJdbcConnectionString(AuditingPolicy wrapper, DatabaseSecurityPolicyProperties properties)
{
string enterUser = Microsoft.Azure.Commands.Sql.Properties.Resources.EnterUserId;
string enterPassword = Microsoft.Azure.Commands.Sql.Properties.Resources.EnterPassword;
return string.Format("jdbc:sqlserver://{0}:{1};database={2};user={3}@{4};password={5};encrypt=true;hostNameInCertificate=*.database.secure.windows.net;loginTimeout=30;",
properties.ProxyDnsName, properties.ProxyPort, wrapper.DatabaseName, enterUser, wrapper.ServerName, enterPassword);
}
private string ConstructOdbcConnectionString(AuditingPolicy wrapper, DatabaseSecurityPolicyProperties properties)
{
string enterUser = Microsoft.Azure.Commands.Sql.Properties.Resources.EnterUserId;
string enterPassword = Microsoft.Azure.Commands.Sql.Properties.Resources.EnterPassword;
StringBuilder sb = new StringBuilder();
sb.Append("Driver={SQL Server Native Client 11.0};");
sb.Append(string.Format("Server=tcp:{0},{1};", properties.ProxyDnsName, properties.ProxyPort));
sb.Append(string.Format("Database={0};", wrapper.DatabaseName));
sb.Append(string.Format("Uid={0}@{1};", enterUser, wrapper.ServerName));
sb.Append(string.Format("Pwd={0};", enterPassword));
sb.Append("Encrypt=yes;Connection Timeout=30;");
return sb.ToString();
}
private string ConstructPhpConnectionString(AuditingPolicy wrapper, DatabaseSecurityPolicyProperties properties)
{
string enterUser = Microsoft.Azure.Commands.Sql.Properties.Resources.EnterUserId;
string enterPassword = Microsoft.Azure.Commands.Sql.Properties.Resources.EnterPassword;
string pdoTitle = Microsoft.Azure.Commands.Sql.Properties.Resources.PdoTitle;
string sqlServerSampleTitle = Microsoft.Azure.Commands.Sql.Properties.Resources.sqlSampleTitle;
string connectionError = Microsoft.Azure.Commands.Sql.Properties.Resources.PhpConnectionError;
StringBuilder sb = new StringBuilder();
sb.Append(string.Format("Server: {0}, {1}", properties.ProxyDnsName, properties.ProxyPort)).Append(Environment.NewLine);
sb.Append(string.Format("SQL Database: {0}", wrapper.DatabaseName)).Append(Environment.NewLine);
sb.Append(string.Format("User Name: {0}", enterUser)).Append(Environment.NewLine).Append(Environment.NewLine);
sb.Append(pdoTitle).Append(Environment.NewLine);
sb.Append("try{").Append(Environment.NewLine);
sb.Append(string.Format("$conn = new PDO ( \"sqlsrv:server = tcp:{0},{1}; Database = \"{2}\", \"{3}\", \"{4}\");",
properties.ProxyDnsName, properties.ProxyPort, wrapper.DatabaseName, enterUser, enterPassword)).Append(Environment.NewLine);
sb.Append("$conn->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );").Append(Environment.NewLine);
sb.Append("}").Append(Environment.NewLine);
sb.Append("catch ( PDOException $e ) {").Append(Environment.NewLine);
sb.Append(string.Format("print( \"{0}\" );", connectionError)).Append(Environment.NewLine);
sb.Append("die(print_r($e));").Append(Environment.NewLine);
sb.Append("}").Append(Environment.NewLine);
sb.Append(sqlServerSampleTitle).Append(Environment.NewLine).Append(Environment.NewLine);
sb.Append(string.Format("connectionInfo = array(\"UID\" => \"{0}@{1}\", \"pwd\" => \"{2}\", \"Database\" => \"{3}\", \"LoginTimeout\" => 30, \"Encrypt\" => 1);",
enterUser, wrapper.ServerName, enterPassword, wrapper.DatabaseName)).Append(Environment.NewLine);
sb.Append(string.Format("$serverName = \"tcp:{0},{1}\";", properties.ProxyDnsName, properties.ProxyPort)).Append(Environment.NewLine);
sb.Append("$conn = sqlsrv_connect($serverName, $connectionInfo);");
return sb.ToString();
}
