private string GetFileName(SecretName name)
{
string fileName = Convert.ToBase64String(Encoding.UTF8.GetBytes(name.ToString())) + ".pjson";
string secretFile = Path.Combine(StoreDirectory, fileName);
return(secretFile);
}
private DataProtector CreateProtector(SecretName name)
{
return(new DpapiNGDataProtector(
_protectionDescriptor,
DpapiSecretStoreProvider.ApplicationName,
SecretStorePurpose,
new[] { name.ToString() }));
}
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");
}
}
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()
{
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));
}
}
private DataProtector CreateProtector(SecretName name)
{
return new DpapiNGDataProtector(
_protectionDescriptor,
DpapiSecretStoreProvider.ApplicationName,
SecretStorePurpose,
new[] { name.ToString() });
}
private string GetFileName(SecretName name)
{
string fileName = Convert.ToBase64String(Encoding.UTF8.GetBytes(name.ToString())) + ".pjson";
string secretFile = Path.Combine(StoreDirectory, fileName);
return secretFile;
}
