private async Task CleanSecrets(SqlConnectionInfo connInfo) { if (Session.CurrentEnvironment == null) { return; } var secrets = await GetEnvironmentSecretStore(Session.CurrentEnvironment); if (secrets == null) { return; } var loginSecretName = new SecretName("sqldb." + connInfo.GetServerName() + ":logins." + User, datacenter: null); var secret = await secrets.Read(loginSecretName, "nucmd db deleteuser"); if (secret != null) { await Console.WriteInfoLine(Strings.Db_DeleteUserCommand_DeletingSecret, loginSecretName.Name); await secrets.Delete(loginSecretName, "nucmd db deleteuser"); } // Check if there is a link that points at this user var match = BaseNameExtractor.Match(User); if (!match.Success) { return; } var userSecretName = new SecretName("sqldb." + connInfo.GetServerName() + "users." + match.Groups["base"].Value); secret = await secrets.Read(userSecretName, "nucmd db deleteuser"); if (String.Equals(secret.Value, loginSecretName.ToString(), StringComparison.OrdinalIgnoreCase)) { await Console.WriteInfoLine(Strings.Db_DeleteUserCommand_DeletingSecret, userSecretName.Name); await secrets.Delete(userSecretName, "nucmd db deleteuser"); } }
protected override async Task OnExecute() { // Open the store var store = await OpenSecretStore(); // Load the cert file var cert = new X509Certificate2(File, String.Empty, X509KeyStorageFlags.Exportable); if (!cert.HasPrivateKey) { await Console.WriteErrorLine(Strings.Secrets_StoreCertCommand_CertificateHasNoPrivateKey); return; } // Save to a string string data = Convert.ToBase64String(cert.Export(X509ContentType.Pkcs12, String.Empty)); // Determine expiry var expiresAt = cert.NotAfter; // Save the certificate secret // Cert thumbprints are universal, no datacenter-scope needed var certKey = new SecretName("cert:" + cert.Thumbprint, null); await Console.WriteInfoLine(Strings.Secrets_StoreCertCommand_SavingCertificate, certKey.Name, expiresAt); if (!WhatIf) { var secret = new Secret(certKey, data, DateTime.UtcNow, expiresAt, SecretType.Certificate); await store.Write(secret, "nucmd storecert"); } await Console.WriteInfoLine(Strings.Secrets_StoreCertCommand_SavingCertificateReference, Key, expiresAt); if (!WhatIf) { var secret = new Secret(new SecretName(Key, Datacenter), certKey.ToString(), DateTime.UtcNow, expiresAt, SecretType.Link); await store.Write(secret, "nucmd storecert"); } }
protected override async Task OnExecute() { if (ExpiresIn != null) { ExpiresAt = DateTime.Now + ExpiresIn.Value; } if (ExpiresAt != null) { ExpiresAt = ExpiresAt.Value.ToUniversalTime(); } else { ExpiresAt = DateTime.UtcNow.AddDays(14); // Two week expiration by default } var connInfo = await GetSqlConnectionInfo(); // Generate the login name string loginName = Name.ToLowerInvariant() + "_" + DateTime.UtcNow.ToString("yyyyMMMdd"); // Generate a password string loginPassword = Utils.GeneratePassword(timestamped: false); // Test connection to Secret Store // We have a current environment because GetSqlConnectionInfo ensures that one exists // GetEnvironmentSecretStore will throw if the store does not exist var secrets = await GetEnvironmentSecretStore(Session.CurrentEnvironment); // Connect to master if (!WhatIf) { IList <string> databases = null; using (var connection = await connInfo.Connect("master")) { var masterConnStr = new SqlConnectionStringBuilder(connection.ConnectionString); await Console.WriteInfoLine(String.Format( CultureInfo.CurrentCulture, Strings.Db_CreateUserCommand_Connected, masterConnStr.DataSource, masterConnStr.InitialCatalog)); // Create the login.\ // Can't use SQL Parameters here unfortunately. But the risk is low: // 1. This is an admin/operations tool, only our administrators will use it // 2. We use a Regex to restrict the Service name and then we derive the login name from that using only safe characters // 3. The password is also derived from safe characters await Console.WriteInfoLine(String.Format( CultureInfo.CurrentCulture, Strings.Db_CreateUserCommand_CreatingLogin, loginName, masterConnStr.DataSource)); await connection.QueryAsync <int>("CREATE LOGIN [" + loginName + "] WITH password='******'"); if (ServerAdmin) { // Make the user a dbmanager await Console.WriteInfoLine(String.Format( CultureInfo.CurrentCulture, Strings.Db_CreateUserCommand_CreatingUser, loginName, masterConnStr.InitialCatalog)); await connection.QueryAsync <int>( "CREATE USER [" + loginName + "] FROM LOGIN [" + loginName + "]"); await Console.WriteInfoLine(String.Format( CultureInfo.CurrentCulture, Strings.Db_CreateUserCommand_ServerManagering, loginName, masterConnStr.DataSource)); await connection.QueryAsync <int>( "EXEC sp_addrolemember 'dbmanager', '" + loginName + "'; " + "EXEC sp_addrolemember 'loginmanager', '" + loginName + "';"); await Console.WriteInfoLine(Strings.Db_CreateUserCommand_FetchingDBs); databases = (await connection.QueryAsync <string>(@" SELECT name FROM sys.databases WHERE name <> 'master' AND name <> @targetDb", new { targetDb = connInfo.ConnectionString.InitialCatalog })).ToList(); await Console.WriteInfoLine(Strings.Db_CreateUserCommand_RetrievedDatabases, databases.Count); } } if (ServerAdmin) { Debug.Assert(databases != null); // Connect to each Database except for the target db and master and make the user a db_owner of that DB foreach (var database in databases) { using (var connection = await connInfo.Connect(database)) { bool isReplica = false; try { await Console.WriteInfoLine(String.Format( CultureInfo.CurrentCulture, Strings.Db_CreateUserCommand_CreatingUser, loginName, database)); await connection.QueryAsync <int>("CREATE USER [" + loginName + "] FROM LOGIN [" + loginName + "]"); await Console.WriteInfoLine(String.Format( CultureInfo.CurrentCulture, Strings.Db_CreateUserCommand_AdminingUser, loginName, database)); await connection.QueryAsync <int>("EXEC sp_addrolemember 'db_owner', '" + loginName + "';"); } catch (SqlException sqlex) { // 40682 - Database is an active secondary replica if (sqlex.Number == 40682) { isReplica = true; } else { throw; } } if (isReplica) { await Console.WriteInfoLine("Skipping {0}, it is an active secondary replica", database); } } } } // Connect to the database itself using (var connection = await connInfo.Connect()) { await Console.WriteInfoLine(String.Format( CultureInfo.CurrentCulture, Strings.Db_CreateUserCommand_Connected, connInfo.ConnectionString.DataSource, connInfo.ConnectionString.InitialCatalog)); // Create the user and grant permissions await Console.WriteInfoLine(String.Format( CultureInfo.CurrentCulture, Strings.Db_CreateUserCommand_CreatingUser, loginName, connInfo.ConnectionString.InitialCatalog)); await connection.QueryAsync <int>( "CREATE USER [" + loginName + "] FROM LOGIN [" + loginName + "]"); if (Schemas == null) { await Console.WriteWarningLine(Strings.Db_CreateUserCommand_NoSchemasSpecified); Schemas = new [] { "dbo" }; } foreach (var schema in Schemas) { await Console.WriteInfoLine(String.Format( CultureInfo.CurrentCulture, Strings.Db_CreateUserCommand_GrantingUser, loginName, schema, connInfo.ConnectionString.InitialCatalog)); await connection.QueryAsync <int>( "GRANT CONTROL ON SCHEMA :: [" + schema + "] TO " + loginName); } } // Generate the connection string var loginConnStr = new SqlConnectionStringBuilder(connInfo.ConnectionString.ConnectionString) { UserID = loginName, Password = loginPassword, ConnectTimeout = 30, Encrypt = true, IntegratedSecurity = false }; // Save the connection string string serverBaseName = "sqldb." + connInfo.GetServerName(); var secretName = new SecretName(serverBaseName + ":logins." + loginName); await Console.WriteInfoLine(Strings.Db_CreateUserCommand_SavingConnectionString, secretName.Name); await secrets.Write(new Secret( secretName, loginConnStr.ConnectionString, DateTime.UtcNow, ExpiresAt, SecretType.Password), "nucmd db createuser"); // Save a link to the full user connection without the timestamp string latestUserSecretName = serverBaseName + ":users." + Name; await secrets.Write(new Secret( new SecretName(latestUserSecretName), secretName.ToString(), DateTime.UtcNow, ExpiresAt, SecretType.Link), "nucmd db createuser"); } else { await Console.WriteInfoLine(String.Format( CultureInfo.CurrentCulture, Strings.Db_CreateUserCommand_WouldCreateUser, connInfo.ConnectionString.DataSource, connInfo.ConnectionString.InitialCatalog, loginName)); } }