private string GetFileName(SecretName name)
{
string fileName = Convert.ToBase64String(Encoding.UTF8.GetBytes(name.ToString())) + ".pjson";
string secretFile = Path.Combine(StoreDirectory, fileName);
return(secretFile);
}
public static bool TryParse(string input, out SecretName parsed)
{
parsed = null;
string[] segments = input.Split(':');
if (segments.Length != 2)
{
return(false);
}
string name = Encoding.UTF8.GetString(Convert.FromBase64String(segments[1]));
if (String.Equals(segments[0], "_", StringComparison.OrdinalIgnoreCase))
{
parsed = new SecretName(name, datacenter: null);
}
else
{
int datacenter;
if (!Int32.TryParse(segments[0], out datacenter))
{
return(false);
}
parsed = new SecretName(name, datacenter);
}
return(true);
}
private DataProtector CreateProtector(SecretName name)
{
return(new DpapiNGDataProtector(
_protectionDescriptor,
DpapiSecretStoreProvider.ApplicationName,
SecretStorePurpose,
new[] { name.ToString() }));
}
internal Secret(SecretName name, string value, DateTime createdUtc, DateTime? expiryUtc, SecretType type, IEnumerable<SecretAuditEntry> auditLog)
{
Name = name;
Value = value;
CreatedUtc = createdUtc;
ExpiryUtc = expiryUtc;
Type = type;
_auditLog = auditLog.ToList();
AuditLog = _auditLog.AsReadOnly();
}
internal Secret(SecretName name, string value, DateTime createdUtc, DateTime?expiryUtc, SecretType type, IEnumerable <SecretAuditEntry> auditLog)
{
Name = name;
Value = value;
CreatedUtc = createdUtc;
ExpiryUtc = expiryUtc;
Type = type;
_auditLog = auditLog.ToList();
AuditLog = _auditLog.AsReadOnly();
}
private async Task <Secret> UnauditedReadSecret(SecretName name, string fileName)
{
if (!File.Exists(fileName))
{
return(null);
}
// Read the file
var protector = CreateProtector(name);
return(JsonFormat.Deserialize <Secret>(await DpapiSecretStoreProvider.ReadSecretFile(fileName, protector)));
}
public override async Task<bool> Delete(SecretName name, string clientOperation)
{
// Write an audit record
var fileName = GetFileName(name);
var existingSecret = await UnauditedReadSecret(name, fileName);
if (existingSecret == null)
{
return false;
}
existingSecret.AddAuditEntry(await SecretAuditEntry.CreateForLocalUser(clientOperation, SecretAuditAction.Deleted));
await UnauditedWriteSecret(existingSecret);
// Change the file extension
File.Move(fileName, Path.ChangeExtension(fileName, ".del"));
return true;
}
public override async Task <bool> Delete(SecretName name, string clientOperation)
{
// Write an audit record
var fileName = GetFileName(name);
var existingSecret = await UnauditedReadSecret(name, fileName);
if (existingSecret == null)
{
return(false);
}
existingSecret.AddAuditEntry(await SecretAuditEntry.CreateForLocalUser(clientOperation, SecretAuditAction.Deleted));
await UnauditedWriteSecret(existingSecret);
// Change the file extension
File.Move(fileName, Path.ChangeExtension(fileName, ".del"));
return(true);
}
public override async Task <Secret> Read(SecretName name, string clientOperation)
{
// Read the secret
var secret = await UnauditedReadSecret(name, GetFileName(name));
if (secret == null)
{
return(null);
}
// Add audit log entry and rewrite
secret.AddAuditEntry(await SecretAuditEntry.CreateForLocalUser(clientOperation, SecretAuditAction.Retrieved));
await UnauditedWriteSecret(secret);
// Return the secret value
return(secret);
}
public override async Task<bool> Undelete(SecretName name, string clientOperation)
{
// Locate the deleted file
var fileName = GetFileName(name);
var deletedName = Path.ChangeExtension(fileName, ".del");
var deletedSecret = await UnauditedReadSecret(name, deletedName);
if (deletedSecret == null)
{
return false;
}
// Write it back to a normal secret file
deletedSecret.AddAuditEntry(await SecretAuditEntry.CreateForLocalUser(clientOperation, SecretAuditAction.Restored));
await UnauditedWriteSecret(deletedSecret);
// Delete the deleted secret :)
File.Delete(deletedName);
return true;
}
public override async Task <bool> Undelete(SecretName name, string clientOperation)
{
// Locate the deleted file
var fileName = GetFileName(name);
var deletedName = Path.ChangeExtension(fileName, ".del");
var deletedSecret = await UnauditedReadSecret(name, deletedName);
if (deletedSecret == null)
{
return(false);
}
// Write it back to a normal secret file
deletedSecret.AddAuditEntry(await SecretAuditEntry.CreateForLocalUser(clientOperation, SecretAuditAction.Restored));
await UnauditedWriteSecret(deletedSecret);
// Delete the deleted secret :)
File.Delete(deletedName);
return(true);
}
public override IEnumerable <SecretListItem> List(bool includeDeleted)
{
var files = Directory.EnumerateFiles(StoreDirectory, "*.pjson").Select(s => Tuple.Create(s, false));
if (includeDeleted)
{
files = Enumerable.Concat(
files,
Directory.EnumerateFiles(StoreDirectory, "*.del").Select(s => Tuple.Create(s, true)));
}
return(files
.Select(t => Tuple.Create(Path.GetFileNameWithoutExtension(t.Item1), t.Item2))
.Where(t => !String.Equals(t.Item1, "metadata.v1", StringComparison.OrdinalIgnoreCase))
.Select(t => new SecretListItem()
{
Name = SecretName.Parse(Encoding.UTF8.GetString(Convert.FromBase64String(t.Item1))),
Deleted = t.Item2
}));
}
public override async Task <IEnumerable <SecretAuditEntry> > ReadAuditLog(SecretName name)
{
// Read the secret
var fileName = GetFileName(name);
var secret = await UnauditedReadSecret(name, fileName);
if (secret == null)
{
// Try to read the deleted log
secret = await UnauditedReadSecret(name, Path.ChangeExtension(fileName, ".del"));
}
// Still null?
if (secret == null)
{
return(Enumerable.Empty <SecretAuditEntry>());
}
// No need to write to the audit log this time. Just return the log in a new collection.
return(new List <SecretAuditEntry>(secret.AuditLog));
}
public static bool TryParse(string input, out SecretName parsed)
{
parsed = null;
string[] segments = input.Split(':');
if (segments.Length != 2)
{
return false;
}
string name = Encoding.UTF8.GetString(Convert.FromBase64String(segments[1]));
if (String.Equals(segments[0], "_", StringComparison.OrdinalIgnoreCase))
{
parsed = new SecretName(name, datacenter: null);
}
else
{
int datacenter;
if (!Int32.TryParse(segments[0], out datacenter))
{
return false;
}
parsed = new SecretName(name, datacenter);
}
return true;
}
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");
}
}
public abstract Task <IEnumerable <SecretAuditEntry> > ReadAuditLog(SecretName key);
public abstract Task <Secret> Read(SecretName key, string clientOperation);
public abstract Task <bool> Undelete(SecretName name, string clientOperation);
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));
}
}
public abstract Task<IEnumerable<SecretAuditEntry>> ReadAuditLog(SecretName key);
public abstract Task<Secret> Read(SecretName key, string clientOperation);
public abstract Task<bool> Undelete(SecretName name, string clientOperation);
public override async Task<IEnumerable<SecretAuditEntry>> ReadAuditLog(SecretName name)
{
// Read the secret
var fileName = GetFileName(name);
var secret = await UnauditedReadSecret(name, fileName);
if (secret == null)
{
// Try to read the deleted log
secret = await UnauditedReadSecret(name, Path.ChangeExtension(fileName, ".del"));
}
// Still null?
if (secret == null)
{
return Enumerable.Empty<SecretAuditEntry>();
}
// No need to write to the audit log this time. Just return the log in a new collection.
return new List<SecretAuditEntry>(secret.AuditLog);
}
public Secret(SecretName name, string value, DateTime createdUtc, DateTime?expiryUtc, SecretType type)
: this(name, value, createdUtc, expiryUtc, type, Enumerable.Empty <SecretAuditEntry>())
{
}
public override async Task<Secret> Read(SecretName name, string clientOperation)
{
// Read the secret
var secret = await UnauditedReadSecret(name, GetFileName(name));
if (secret == null)
{
return null;
}
// Add audit log entry and rewrite
secret.AddAuditEntry(await SecretAuditEntry.CreateForLocalUser(clientOperation, SecretAuditAction.Retrieved));
await UnauditedWriteSecret(secret);
// Return the secret value
return secret;
}
public Secret(SecretName name, string value, DateTime createdUtc, DateTime? expiryUtc, SecretType type)
: this(name, value, createdUtc, expiryUtc, type, Enumerable.Empty<SecretAuditEntry>())
{
}
private async Task<Secret> UnauditedReadSecret(SecretName name, string fileName)
{
if (!File.Exists(fileName))
{
return null;
}
// Read the file
var protector = CreateProtector(name);
return JsonFormat.Deserialize<Secret>(await DpapiSecretStoreProvider.ReadSecretFile(fileName, protector));
}
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() });
}
