public OperationResult AddLogin(string serverAddress, string username, string password, string loginUsername, string loginPassword) { if (string.IsNullOrWhiteSpace(serverAddress)) { throw new ArgumentNullException(nameof(serverAddress)); } if (string.IsNullOrWhiteSpace(username)) { throw new ArgumentNullException(nameof(username)); } if (string.IsNullOrWhiteSpace(password)) { throw new ArgumentNullException(nameof(password)); } if (string.IsNullOrWhiteSpace(loginUsername)) { throw new ArgumentNullException(nameof(loginUsername)); } if (string.IsNullOrWhiteSpace(loginPassword)) { throw new ArgumentNullException(nameof(loginPassword)); } var hasInvalidSymbols = SqlStringValidator.HasInvalidSymbols(loginUsername) || SqlStringValidator.HasInvalidSymbols(loginPassword); if (hasInvalidSymbols) { return(new OperationResult(false, "The username or password contains illegal characters")); } var isInjectionAttempt = SqlStringValidator.HasInjectionCommand(loginUsername) || SqlStringValidator.HasInjectionCommand(loginPassword); if (isInjectionAttempt) { return(new OperationResult(false, "The username or password contains illegal characters")); } var connectionString = ConnectionStringBuilder.FromCredentials(serverAddress, username, password); using (var connection = new SqlConnection(connectionString)) { connection.Open(); using (var command = new SqlCommand($"CREATE LOGIN [{loginUsername}] WITH PASSWORD = '******'", connection)) { command.ExecuteNonQuery(); } } return(new OperationResult(true)); }
public ConnectionResult ValidateConnection(string serverAddress, string username, string password) { const string CONNECTION_COULD_NOT_BE_ESTABLISHED_CODE = "error: 40 - Could not open a connection to SQL Server"; const string BLOCKED_BY_FIREWALL_CODE = "is not allowed to access the server. To enable access, use the Windows Azure Management Portal or run sp_set_firewall_rule"; if (string.IsNullOrWhiteSpace(serverAddress)) { throw new ArgumentNullException(nameof(serverAddress)); } if (string.IsNullOrWhiteSpace(username)) { throw new ArgumentNullException(nameof(username)); } if (string.IsNullOrWhiteSpace(password)) { throw new ArgumentNullException(nameof(password)); } var connectionString = ConnectionStringBuilder.FromCredentials(serverAddress, username, password); try { using (var connection = new SqlConnection(connectionString)) { connection.Open(); } } catch (SqlException e) { if (e.Message.Contains(CONNECTION_COULD_NOT_BE_ESTABLISHED_CODE)) { return(new ConnectionResult(false, "The target server is either not turned on, accepting connections or doesn't exist. No connection could be opened. Error: 40")); } if (e.Message.Contains(BLOCKED_BY_FIREWALL_CODE)) { return(new ConnectionResult(false, "The target server is refusing connections from this IP. To enable access use the Windows Azure Management Portal or run sp_set_firewall_rule")); } throw; } return(new ConnectionResult(true)); }
public List <ServerLogin> GetLoginsForServer(string serverAddress, string username, string password) { if (string.IsNullOrWhiteSpace(serverAddress)) { throw new ArgumentNullException(nameof(serverAddress)); } if (string.IsNullOrWhiteSpace(username)) { throw new ArgumentNullException(nameof(username)); } if (string.IsNullOrWhiteSpace(password)) { throw new ArgumentNullException(nameof(password)); } var logins = new List <ServerLogin>(); var connectionString = ConnectionStringBuilder.FromCredentials(serverAddress, username, password); using (var connection = new SqlConnection(connectionString)) { connection.Open(); using (var command = new SqlCommand("SELECT * FROM sys.sql_logins", connection)) using (var reader = command.ExecuteReader()) { while (reader.Read()) { var userName = reader.GetString(0); var principalId = reader.GetInt32(1); var isDisabled = reader.GetBoolean(5); var created = reader.GetDateTime(6); var lastModified = reader.GetDateTime(7); var defaultDatabase = reader.GetString(8); logins.Add(new ServerLogin(userName, principalId, isDisabled, created, lastModified, defaultDatabase)); } } } return(logins); }
public List <Database> GetDatabasesForServer(string serverAddress, string username, string password) { if (string.IsNullOrWhiteSpace(serverAddress)) { throw new ArgumentNullException(nameof(serverAddress)); } if (string.IsNullOrWhiteSpace(username)) { throw new ArgumentNullException(nameof(username)); } if (string.IsNullOrWhiteSpace(password)) { throw new ArgumentNullException(nameof(password)); } var databases = new List <Database>(); var connectionString = ConnectionStringBuilder.FromCredentials(serverAddress, username, password); using (var connection = new SqlConnection(connectionString)) { connection.Open(); using (var command = new SqlCommand("SELECT * FROM sys.databases", connection)) using (var reader = command.ExecuteReader()) { while (reader.Read()) { var databaseName = reader.GetString(0); databases.Add(new Database(databaseName)); } } } return(databases); }
public OperationResult DeleteRoleForUser(string serverAddress, string username, string password, string catalog, string loginUsername, string roleName) { if (string.IsNullOrWhiteSpace(serverAddress)) { throw new ArgumentNullException(nameof(serverAddress)); } if (string.IsNullOrWhiteSpace(username)) { throw new ArgumentNullException(nameof(username)); } if (string.IsNullOrWhiteSpace(password)) { throw new ArgumentNullException(nameof(password)); } if (string.IsNullOrWhiteSpace(loginUsername)) { throw new ArgumentNullException(nameof(loginUsername)); } var hasInvalidSymbols = SqlStringValidator.HasInvalidSymbols(loginUsername); if (hasInvalidSymbols) { return(new OperationResult(false, "The username contains illegal characters")); } var isInjectionAttempt = SqlStringValidator.HasInjectionCommand(loginUsername); if (isInjectionAttempt) { return(new OperationResult(false, "The username contains illegal characters")); } var isValidRole = SqlStringValidator.IsValidRole(roleName); if (!isValidRole) { return(new OperationResult(false, "The given role does not exist in SQL")); } var connectionString = ConnectionStringBuilder.FromCredentials(serverAddress, username, password, catalog); using (var connection = new SqlConnection(connectionString)) { connection.Open(); var hasCatalogUser = HasUserInDatabase(loginUsername, connection); if (!hasCatalogUser) { return(new OperationResult(false, "The given user does not have this role in the target database")); } using (var command = new SqlCommand("sp_droprolemember", connection)) { command.CommandType = CommandType.StoredProcedure; command.Parameters .Add("@membername", loginUsername) .Add("@rolename", roleName); using (var _ = command.ExecuteReader()) { // Nothing much to do here } } const string ROLES_IN_DATABASE_SQL = "SELECT COUNT(*) FROM sys.database_role_members dbrolemembers " + "INNER JOIN sys.database_principals dbprincipals ON dbprincipals.principal_id = dbrolemembers.role_principal_id " + "INNER JOIN sys.database_principals dbuserprincipals ON dbuserprincipals.principal_id = dbrolemembers.member_principal_id " + "WHERE dbuserprincipals.name = @loginUsername"; bool hasOtherRolesInDatabase; using (var command = new SqlCommand(ROLES_IN_DATABASE_SQL, connection)) { command.Parameters .Add("@loginUsername", loginUsername); using (var reader = command.ExecuteReader()) { reader.Read(); hasOtherRolesInDatabase = reader.GetInt32(0) >= 1; } } if (!hasOtherRolesInDatabase) { using (var command = new SqlCommand($"DROP USER [{loginUsername}]", connection)) { command.ExecuteNonQuery(); } } } return(new OperationResult(true)); }
public OperationResult AddRoleForUser(string serverAddress, string username, string password, string catalog, string loginUsername, string roleName) { if (string.IsNullOrWhiteSpace(serverAddress)) { throw new ArgumentNullException(nameof(serverAddress)); } if (string.IsNullOrWhiteSpace(username)) { throw new ArgumentNullException(nameof(username)); } if (string.IsNullOrWhiteSpace(password)) { throw new ArgumentNullException(nameof(password)); } if (string.IsNullOrWhiteSpace(loginUsername)) { throw new ArgumentNullException(nameof(loginUsername)); } var hasInvalidSymbols = SqlStringValidator.HasInvalidSymbols(loginUsername); if (hasInvalidSymbols) { return(new OperationResult(false, "The username contains illegal characters")); } var isInjectionAttempt = SqlStringValidator.HasInjectionCommand(loginUsername); if (isInjectionAttempt) { return(new OperationResult(false, "The username contains illegal characters")); } var isValidRole = SqlStringValidator.IsValidRole(roleName); if (!isValidRole) { return(new OperationResult(false, "The given role does not exist in SQL")); } var connectionString = ConnectionStringBuilder.FromCredentials(serverAddress, username, password, catalog); using (var connection = new SqlConnection(connectionString)) { connection.Open(); var hasCatalogUser = HasUserInDatabase(loginUsername, connection); if (!hasCatalogUser) { // If the user doesn't have a mapping, we need to create a user // in the target database prior to adding the role using (var command = new SqlCommand($"CREATE USER [{loginUsername}] FOR LOGIN {loginUsername}", connection)) { command.ExecuteNonQuery(); } } using (var command = new SqlCommand("sp_addrolemember", connection)) { command.CommandType = CommandType.StoredProcedure; command.Parameters .Add("@membername", loginUsername) .Add("@rolename", roleName); using (var _ = command.ExecuteReader()) { // Nothing much to do here } } } return(new OperationResult(true)); }
public List <DatabaseRole> GetRolesForUserInDatabase(string serverAddress, string username, string password, string catalog, string loginUsername) { if (string.IsNullOrWhiteSpace(serverAddress)) { throw new ArgumentNullException(nameof(serverAddress)); } if (string.IsNullOrWhiteSpace(username)) { throw new ArgumentNullException(nameof(username)); } if (string.IsNullOrWhiteSpace(password)) { throw new ArgumentNullException(nameof(password)); } if (string.IsNullOrWhiteSpace(catalog)) { throw new ArgumentNullException(nameof(catalog)); } if (string.IsNullOrWhiteSpace(loginUsername)) { throw new ArgumentNullException(nameof(loginUsername)); } var connectionString = ConnectionStringBuilder.FromCredentials(serverAddress, username, password, catalog); var roles = new List <DatabaseRole> { new DatabaseRole(SqlConstants.OWNER), new DatabaseRole(SqlConstants.ACCESS_ADMIN), new DatabaseRole(SqlConstants.SECURITY_ADMIN), new DatabaseRole(SqlConstants.DDL_ADMIN), new DatabaseRole(SqlConstants.BACKUP_OPERATOR), new DatabaseRole(SqlConstants.DATA_READER), new DatabaseRole(SqlConstants.DATA_WRITER), new DatabaseRole(SqlConstants.DENY_DATA_READER), new DatabaseRole(SqlConstants.DENY_DATA_WRITER), }; using (var connection = new SqlConnection(connectionString)) { connection.Open(); const string SQL = "SELECT dbprincipals.name AS RoleName, dbuserprincipals.name AS Username FROM sys.database_role_members dbrolemembers " + "INNER JOIN sys.database_principals dbprincipals ON dbprincipals.principal_id = dbrolemembers.role_principal_id " + "INNER JOIN sys.database_principals dbuserprincipals ON dbuserprincipals.principal_id = dbrolemembers.member_principal_id " + "WHERE dbuserprincipals.name = @loginUsername"; using (var command = new SqlCommand(SQL, connection)) { command.Parameters.Add("@loginUsername", loginUsername); using (var reader = command.ExecuteReader()) { while (reader.Read()) { var ownedRole = reader.GetString(0); var role = roles.SingleOrDefault(x => x.RawSqlRoleName == ownedRole); if (role == null) { continue; // Unsupported by client } role.Value = true; } } } } return(roles); }