/// <summary> /// Initializes a new instance of the <see cref="ImpersonationSqlFileStream" /> class. /// </summary> public ImpersonationSqlFileStream( ) { NetworkCredential databaseCredential; ///// // Determine if an impersonation context is required. ///// using (var context = DatabaseContext.GetContext( )) { databaseCredential = context.DatabaseInfo.Credentials; } if (databaseCredential != null) { WindowsIdentity windowsIdentity = WindowsIdentity.GetCurrent( ); bool impersonate = false; if (windowsIdentity != null) { var principal = new WindowsPrincipal(windowsIdentity); string account = (( WindowsIdentity )principal.Identity).Name; if (String.Compare(CredentialHelper.GetFullyQualifiedName(databaseCredential), account, StringComparison.OrdinalIgnoreCase) != 0) { impersonate = true; } } if (impersonate) { _impersonationContext = ImpersonationContext.GetContext(databaseCredential); if (string.IsNullOrEmpty(databaseCredential.Domain)) { windowsIdentity = WindowsIdentity.GetCurrent( ); if (windowsIdentity != null) { var principal = new WindowsPrincipal(windowsIdentity); string account = (( WindowsIdentity )principal.Identity).Name; var parts = account.Split(new[] { '\\' }, StringSplitOptions.RemoveEmptyEntries); if (parts.Length == 2) { databaseCredential.Domain = parts[0]; } } } } } }
/// <summary> /// Initializes a new instance of the <see cref="DatabaseImpersonationContext" /> class. /// </summary> public DatabaseImpersonationContext( ) { NetworkCredential credential; using (var context = DatabaseContext.GetContext( )) { credential = context.DatabaseInfo.Credentials; } if (credential != null) { _impersonationContext = ImpersonationContext.GetContext(credential); } }
/// <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( ); } } }
/// <summary> /// Creates and opens a database connection using the specified properties. /// </summary> /// <param name="databaseInfo">An object describing the database properties.</param> /// <exception cref="InvalidOperationException"></exception> /// <returns> /// An open database connection. /// </returns> /// <exception cref="System.ArgumentNullException">databaseInfo</exception> /// <exception cref="System.InvalidOperationException">The specified database provider is not supported.</exception> /// <exception cref="System.Exception">Cannot open the database connection.</exception> private static IDbConnection CreateConnection(DatabaseInfo databaseInfo) { if (databaseInfo == null) { throw new ArgumentNullException("databaseInfo"); } IDbConnection connection = null; try { // Attempt to open a connection to the database connection = new SqlConnection(databaseInfo.ConnectionString); bool impersonate = false; if (databaseInfo.Authentication == DatabaseAuthentication.Integrated) { if (databaseInfo.Credentials != null) { impersonate = true; // 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(databaseInfo.Credentials), account, StringComparison.OrdinalIgnoreCase) == 0) { impersonate = false; } } } } // Open the database connection (impersonate if required) if (impersonate) { using (ImpersonationContext.GetContext(databaseInfo.Credentials)) { connection.Open(); } } else { connection.Open(); } } catch (Exception) { if (connection != null) { connection.Close(); } throw; } return(connection); }