/// <summary>
/// Overrides the specified server.
/// </summary>
/// <param name="server">The server.</param>
/// <param name="database">The database.</param>
/// <param name="username">The username.</param>
/// <param name="password">The password.</param>
/// <returns></returns>
public static DatabaseOverride Override(string server, string database, string username, string password)
{
if (string.IsNullOrEmpty(username))
{
return(Override(server, database));
}
NetworkCredential credential = CredentialHelper.ConvertToNetworkCredential(username, password);
DatabaseInfo databaseSettings = new SqlDatabaseInfo(server, database, DatabaseAuthentication.Integrated, credential);
return(Override(databaseSettings));
}
/// <summary>
/// Adds the database user.
/// </summary>
/// <param name="username">The username.</param>
/// <param name="role">The role.</param>
/// <param name="server">The server.</param>
/// <param name="catalog">The catalog.</param>
/// <param name="dbUser">The database user.</param>
/// <param name="dbPassword">The database password.</param>
/// <exception cref="ArgumentNullException"></exception>
public static void AddDatabaseUser(string username, string role, string server, string catalog, string dbUser, string dbPassword)
{
if (string.IsNullOrEmpty(username) || string.IsNullOrEmpty(server) || string.IsNullOrEmpty(catalog))
{
throw new ArgumentNullException( );
}
bool impersonate = false;
var credential = new NetworkCredential( );
if (!string.IsNullOrEmpty(dbUser))
{
credential = CredentialHelper.ConvertToNetworkCredential(dbUser, dbPassword);
/////
// Check if the context identity matches the current windows identity
/////
WindowsIdentity windowsIdentity = WindowsIdentity.GetCurrent( );
var principal = new WindowsPrincipal(windowsIdentity);
string account = (( WindowsIdentity )principal.Identity).Name;
if (String.Compare(CredentialHelper.GetFullyQualifiedName(credential), account, StringComparison.OrdinalIgnoreCase) != 0)
{
impersonate = true;
}
}
ImpersonationContext impersonationContext = null;
/////
// Format up the connection string
/////
string connectionString = GetDatabaseConnectionString(server);
try
{
if (impersonate)
{
impersonationContext = ImpersonationContext.GetContext(credential);
}
using (var platformDbConnection = new SqlConnection(connectionString))
{
platformDbConnection.Open( );
using (SqlCommand sqlCommand = platformDbConnection.CreateCommand( ))
{
/////
// If specific user exists then delete it
/////
sqlCommand.CommandText = string.Format(@"DECLARE @login NVARCHAR(MAX) = NULL; SELECT @login = name FROM sys.server_principals WHERE LOWER(name) = LOWER(N'{0}'); IF (@login IS NULL) CREATE LOGIN [{0}] FROM WINDOWS; GRANT VIEW SERVER STATE TO [{0}]", username);
sqlCommand.ExecuteNonQuery( );
}
platformDbConnection.Close( );
}
/////
// Connect to the platform database and add in the new user to the database role.
/////
connectionString = $@"Server=tcp:{server};Integrated security=SSPI;database={catalog}";
using (var platformDbConnection = new SqlConnection(connectionString))
{
platformDbConnection.Open( );
using (SqlCommand sqlCommand = platformDbConnection.CreateCommand( ))
{
// If specific user exists then delete it
sqlCommand.CommandText = string.Format(@"DECLARE @user NVARCHAR(MAX) = NULL; SELECT @user = name FROM sys.database_principals WHERE LOWER(name) = LOWER(N'{0}'); IF (@user IS NOT NULL) EXEC ('ALTER USER [' + @user + '] WITH LOGIN = [{0}]') ELSE CREATE USER [{0}] FOR LOGIN [{0}]", username);
sqlCommand.ExecuteNonQuery( );
/////
// Assign the role for the user
/////
sqlCommand.CommandText = string.Format(@"exec sp_addrolemember N'{1}', N'{0}'", username, role);
sqlCommand.ExecuteNonQuery( );
}
platformDbConnection.Close( );
}
}
finally
{
impersonationContext?.Dispose( );
}
}
/// <summary>
/// Deploys the specified database.
/// </summary>
/// <param name="dacpacPath">The DacPac path.</param>
/// <param name="serverName">Name of the server.</param>
/// <param name="databaseName">Name of the database.</param>
/// <param name="filePrefix">The file prefix.</param>
/// <param name="dbUser">The database user.</param>
/// <param name="dbPassword">The database password.</param>
/// <param name="mdfDirectory">The MDF directory.</param>
/// <param name="ldfDirectory">The LDF directory.</param>
/// <param name="logger">The logger.</param>
/// <exception cref="System.ArgumentNullException">dacpacPath</exception>
/// <exception cref="System.IO.FileNotFoundException">Specified DacPac file does not exist</exception>
/// <exception cref="System.IO.DirectoryNotFoundException"></exception>
/// <remarks>
/// Both the mdfPath and ldfPath values have to be set to invoke the DatabaseCreationLocationModifier.
/// </remarks>
public static void DeployDatabase(string dacpacPath, string serverName = "localhost", string databaseName = "SoftwarePlatform", string filePrefix = "SoftwarePlatform", string dbUser = null, string dbPassword = null, string mdfDirectory = null, string ldfDirectory = null, Action <string> logger = null)
{
if (string.IsNullOrEmpty(dacpacPath))
{
throw new ArgumentNullException(nameof(dacpacPath));
}
if (!File.Exists(dacpacPath))
{
throw new FileNotFoundException("Specified DacPac file does not exist", dacpacPath);
}
if (string.IsNullOrEmpty(serverName))
{
serverName = "localhost";
}
if (string.IsNullOrEmpty(databaseName))
{
databaseName = "SoftwarePlatform";
}
if (string.IsNullOrEmpty(filePrefix))
{
filePrefix = "SoftwarePlatform";
}
bool databaseCreationLocationModifierActive = !string.IsNullOrEmpty(mdfDirectory) && !string.IsNullOrEmpty(ldfDirectory);
var contributors = new List <string>( );
if (databaseCreationLocationModifierActive)
{
/////
//Contributor to set the MDF and LDF file locations.
/////
contributors.Add("ReadiNowDeploymentPlanContributors.DatabaseCreationLocationModifier");
}
var contributorArguments = new Dictionary <string, string>( );
if (databaseCreationLocationModifierActive)
{
/////
// Set the file paths.
/////
string mdfFileName = string.Format("{0}_Dat.mdf", filePrefix);
string ldfFileName = string.Format("{0}_Log.ldf", filePrefix);
string mdfFilePath = Path.Combine(mdfDirectory, mdfFileName);
string ldfFilePath = Path.Combine(ldfDirectory, ldfFileName);
contributorArguments.Add("DatabaseCreationLocationModifier.MdfFilePath", mdfFilePath);
contributorArguments.Add("DatabaseCreationLocationModifier.LdfFilePath", ldfFilePath);
}
var options = new DacDeployOptions
{
BlockOnPossibleDataLoss = false
};
if (contributors.Count > 0)
{
/////
// Add any contributors.
/////
options.AdditionalDeploymentContributors = string.Join(";", contributors);
if (contributorArguments.Count > 0)
{
/////
// Add any contributor arguments.
/////
options.AdditionalDeploymentContributorArguments = string.Join(";", contributorArguments.Select(arg => string.Format("{0}={1}", arg.Key, arg.Value)));
}
}
bool impersonate = false;
var credential = new NetworkCredential( );
if (!string.IsNullOrEmpty(dbUser))
{
credential = CredentialHelper.ConvertToNetworkCredential(dbUser, dbPassword);
/////
// Check if the context identity matches the current windows identity
/////
WindowsIdentity windowsIdentity = WindowsIdentity.GetCurrent( );
if (windowsIdentity != null)
{
var principal = new WindowsPrincipal(windowsIdentity);
string account = (( WindowsIdentity )principal.Identity).Name;
if (String.Compare(CredentialHelper.GetFullyQualifiedName(credential), account, StringComparison.OrdinalIgnoreCase) != 0)
{
impersonate = true;
}
}
}
ImpersonationContext impersonationContext = null;
try
{
using (DacPackage dacpac = DacPackage.Load(dacpacPath, DacSchemaModelStorageType.Memory))
{
if (impersonate)
{
impersonationContext = ImpersonationContext.GetContext(credential);
}
string connectionString = "Data Source=" + serverName + ";Integrated Security=True";
var dacServices = new DacServices(connectionString);
dacServices.Message += (sender, e) => LogDacpacMessage(e, logger);
dacServices.Deploy(dacpac, databaseName, true, options);
}
}
catch (DacServicesException exc)
{
DacMessage directoryNotFoundMessage = exc.Messages.FirstOrDefault(message => message.MessageType == DacMessageType.Error && message.Number == 72014);
if (directoryNotFoundMessage != null)
{
var pathRegex = new Regex("Directory lookup for the file \"(.*)?\" failed");
Match match = pathRegex.Match(directoryNotFoundMessage.Message);
if (match.Success)
{
string directory = Path.GetDirectoryName(match.Groups[1].Value);
throw new DirectoryNotFoundException(string.Format("Directory '{0}' was not found. Please create it prior to deploying the database.", directory), exc);
}
}
throw;
}
finally
{
if (impersonationContext != null)
{
impersonationContext.Dispose( );
}
}
}