public void SaveAndReloadCredentials()
{
var originalCredentials = new DataAccessCredentials(CatalogueRepository, "bob");
try
{
originalCredentials.Name = "bob1";
originalCredentials.Username = "******";
originalCredentials.Password = "******";
originalCredentials.SaveToDatabase();
var newCopy = CatalogueRepository.GetObjectByID <DataAccessCredentials>(originalCredentials.ID);
Assert.AreEqual(originalCredentials.Name, newCopy.Name);
Assert.AreEqual(originalCredentials.Username, newCopy.Username);
Assert.AreEqual(originalCredentials.Password, newCopy.Password);
//test overridden Equals
Assert.AreEqual(originalCredentials, newCopy);
originalCredentials.Password = "******";
Assert.AreEqual(originalCredentials, newCopy);//they are still equal because IDs are the same
}
finally
{
originalCredentials.DeleteInDatabase();
}
}
public void CreateNewCredentialsThenGetByUsernamePasswordCombo()
{
var newCredentials = new DataAccessCredentials(CatalogueRepository, "bob");
newCredentials.Username = "******";
newCredentials.Password = "******";
newCredentials.SaveToDatabase();
var newCopy = CatalogueRepository.GetAllObjects <DataAccessCredentials>("WHERE Username='******'").SingleOrDefault();
Assert.IsNotNull(newCopy);
try
{
Assert.NotNull(newCopy);
Assert.AreEqual(newCredentials.ID, newCopy.ID);
Assert.AreEqual(newCredentials.Username, newCopy.Username);
Assert.AreEqual(newCredentials.GetDecryptedPassword(), newCopy.GetDecryptedPassword());
Assert.AreEqual(newCredentials.Password, newCopy.Password);
}
finally
{
newCredentials.DeleteInDatabase();
}
}
public void GetCredentialsFromATableInfo()
{
TableInfo tableInfo = new TableInfo(CatalogueRepository, "GetCredentialsFromATableInfo");
tableInfo.Name = "My Exciting Table";
var creds = new DataAccessCredentials(CatalogueRepository);
try
{
creds.Name = "Test";
creds.SaveToDatabase();
tableInfo.SetCredentials(creds, DataAccessContext.InternalDataProcessing);
tableInfo.SaveToDatabase();
//Go via TableInfo and get credentials
DataAccessCredentials creds2 = (DataAccessCredentials)tableInfo.GetCredentialsIfExists(DataAccessContext.InternalDataProcessing);
Assert.AreEqual(creds2.Name, creds.Name);
}
finally
{
tableInfo.DeleteInDatabase();
creds.DeleteInDatabase();
}
}
public void TestThe_Any_EnumValue()
{
TableInfo tableInfo = new TableInfo(CatalogueRepository, "GetCredentialsFromATableInfo");
tableInfo.Name = "My Exciting Table";
tableInfo.SaveToDatabase();
var creds = new DataAccessCredentials(CatalogueRepository);
try
{
creds.Name = "Test";
creds.SaveToDatabase();
//now create the association as Any
tableInfo.SetCredentials(creds, DataAccessContext.Any);
//because the credential is liscenced to be used under ANY context, you can make requests under any of the specific contexts and be served the Any result
var creds2 = tableInfo.GetCredentialsIfExists(DataAccessContext.InternalDataProcessing);
Assert.NotNull(creds2);
creds2 = tableInfo.GetCredentialsIfExists(DataAccessContext.DataExport);
Assert.NotNull(creds2);
creds2 = tableInfo.GetCredentialsIfExists(DataAccessContext.DataLoad);
Assert.NotNull(creds2);
}
finally
{
tableInfo.DeleteInDatabase();
creds.DeleteInDatabase();
}
}
public void Test_Any_PrioritisingTheMoreAppropriateCredential()
{
TableInfo tableInfo = new TableInfo(CatalogueRepository, "GetCredentialsFromATableInfo");
tableInfo.Name = "Tableinfo1";
tableInfo.SaveToDatabase();
var creds = new DataAccessCredentials(CatalogueRepository);
var creds2 = new DataAccessCredentials(CatalogueRepository);
try
{
creds.Name = "Test";
creds.SaveToDatabase();
//now create the association as Any
tableInfo.SetCredentials(creds, DataAccessContext.DataLoad);
tableInfo.SetCredentials(creds2, DataAccessContext.Any);
Assert.AreEqual(creds, tableInfo.GetCredentialsIfExists(DataAccessContext.DataLoad));
}
finally
{
tableInfo.DeleteInDatabase();
creds.DeleteInDatabase();
creds2.DeleteInDatabase();
}
}
public void TestThe_Any_EnumValue_CannotRequestAnyCredentials()
{
TableInfo tableInfo = new TableInfo(CatalogueRepository, "GetCredentialsFromATableInfo");
tableInfo.Name = "My Exciting Table";
var creds = new DataAccessCredentials(CatalogueRepository);
try
{
creds.Name = "Test";
creds.SaveToDatabase();
tableInfo.SetCredentials(creds, DataAccessContext.InternalDataProcessing);
tableInfo.SaveToDatabase();
//attempt to request ANY credentials
var ex = Assert.Throws <Exception>(() => tableInfo.GetCredentialsIfExists(DataAccessContext.Any));
Assert.AreEqual("You cannot ask for any credentials, you must supply a usage context.", ex.Message);
}
finally
{
tableInfo.DeleteInDatabase();
creds.DeleteInDatabase();
}
}
public void TestGettingConnectionStrings()
{
foreach (TableInfo tbl in CatalogueRepository.GetAllObjects <TableInfo>().Where(table => table.Name.ToLower().Equals("bob")))
{
tbl.DeleteInDatabase();
}
foreach (var c in CatalogueRepository.GetAllObjects <DataAccessCredentials>().Where(cred => cred.Name.ToLower().Equals("bob")))
{
c.DeleteInDatabase();
}
//test it with TableInfos
TableInfo t = new TableInfo(CatalogueRepository, "Bob");
try
{
t.Server = "fish";
t.Database = "bobsDatabase";
t.SaveToDatabase();
//t has no credentials
var server = DataAccessPortal.GetInstance().ExpectServer(t, DataAccessContext.InternalDataProcessing);
Assert.AreEqual(typeof(SqlConnectionStringBuilder), server.Builder.GetType());
Assert.AreEqual("fish", ((SqlConnectionStringBuilder)server.Builder).DataSource);
Assert.AreEqual("bobsDatabase", ((SqlConnectionStringBuilder)server.Builder).InitialCatalog);
Assert.AreEqual(true, ((SqlConnectionStringBuilder)server.Builder).IntegratedSecurity);
var creds = new DataAccessCredentials(CatalogueRepository, "Bob");
try
{
t.SetCredentials(creds, DataAccessContext.InternalDataProcessing, true);
creds.Username = "******";
creds.Password = "******";
creds.SaveToDatabase();
////t has some credentials now
server = DataAccessPortal.GetInstance().ExpectServer(t, DataAccessContext.InternalDataProcessing);
Assert.AreEqual(typeof(SqlConnectionStringBuilder), server.Builder.GetType());
Assert.AreEqual("fish", ((SqlConnectionStringBuilder)server.Builder).DataSource);
Assert.AreEqual("bobsDatabase", ((SqlConnectionStringBuilder)server.Builder).InitialCatalog);
Assert.AreEqual("frank", ((SqlConnectionStringBuilder)server.Builder).UserID);
Assert.AreEqual("bobsPassword", ((SqlConnectionStringBuilder)server.Builder).Password);
Assert.AreEqual(false, ((SqlConnectionStringBuilder)server.Builder).IntegratedSecurity);
}
finally
{
var linker = new TableInfoCredentialsManager(CatalogueRepository);
linker.BreakAllLinksBetween(creds, t);
creds.DeleteInDatabase();
}
}
finally
{
t.DeleteInDatabase();
}
}
public void Test_BlankPasswords()
{
var creds = new DataAccessCredentials(CatalogueRepository, "blankpwdCreds");
creds.Username = "******";
creds.Password = "";
creds.SaveToDatabase();
var manager = new TableInfoCredentialsManager(CatalogueRepository);
Assert.AreEqual(creds, manager.GetCredentialByUsernameAndPasswordIfExists("Root", null));
Assert.AreEqual(creds, manager.GetCredentialByUsernameAndPasswordIfExists("Root", ""));
}
public void DataAccessCredentialsEncryption()
{
//cleanup
foreach (var c in CatalogueRepository.GetAllObjects <DataAccessCredentials>().Where(c => c.Name.Equals("frankieFran")))
{
c.DeleteInDatabase();
}
DataAccessCredentials creds = new DataAccessCredentials(CatalogueRepository, "frankieFran");
try
{
//as soon as you set a password it should be encrypted by the credentials class in memory
creds.Password = "******";
Assert.AreNotEqual("fish", creds.Password);
Assert.AreEqual("fish", creds.GetDecryptedPassword());//but we should still be able to decrypt it
//save it
creds.SaveToDatabase();
using (var con = CatalogueRepository.GetConnection())
{
string value;
using (var cmd = DatabaseCommandHelper.GetCommand("Select Password from DataAccessCredentials where Name='frankieFran'", con.Connection, con.Transaction))
value = (string)cmd.ExecuteScalar();
//ensure password in database is encrypted
Assert.AreNotEqual("fish", value);
Assert.AreEqual(creds.Password, value);//does value in database match value in memory (encrypted)
}
//get a new copy out of the database
DataAccessCredentials newCopy = CatalogueRepository.GetObjectByID <DataAccessCredentials>(creds.ID);
Assert.AreEqual(creds.Password, newCopy.Password); //passwords should match
Assert.AreNotEqual("fish", creds.Password); //neither should be fish
Assert.AreNotEqual("fish", newCopy.Password);
//both should decrypt to the same value (fish
Assert.AreEqual("fish", creds.GetDecryptedPassword());
Assert.AreEqual("fish", newCopy.GetDecryptedPassword());
}
finally
{
creds.DeleteInDatabase();
}
}
public void GetConnectionStringFromCatalogueWhereOneTableInfoUsesACredentialsOverride()
{
Catalogue c = new Catalogue(CatalogueRepository, "GetConnectionStringFromCatalogueWhereOneTableInfoUsesACredentialsOverride");
CatalogueItem ci = new CatalogueItem(CatalogueRepository, c, "GetConnectionStringFromCatalogueWhereOneTableInfoUsesACredentialsOverride");
TableInfo t = new TableInfo(CatalogueRepository, "Test");
ColumnInfo col = new ColumnInfo(CatalogueRepository, "[mydatabase].dbo.test.col", "varchar(10)", t);
var extractionInformation = new ExtractionInformation(CatalogueRepository, ci, col, col.Name);
DataAccessCredentials cred = null;
try
{
t.Server = "myserver";
t.Database = "mydatabase";
cred = new DataAccessCredentials(CatalogueRepository, "bob");
cred.Username = "******";
cred.Password = "******";
Assert.AreNotEqual("pass", cred.Password);
Assert.AreEqual("pass", cred.GetDecryptedPassword());
cred.SaveToDatabase();
t.SetCredentials(cred, DataAccessContext.InternalDataProcessing);
t.SaveToDatabase();
var constr = (SqlConnectionStringBuilder)c.GetDistinctLiveDatabaseServer(DataAccessContext.InternalDataProcessing, false).Builder;
Assert.AreEqual("myserver", constr.DataSource);
Assert.False(constr.IntegratedSecurity);
Assert.AreEqual("bob", constr.UserID);
Assert.AreEqual("pass", constr.Password);
}
finally
{
t.DeleteInDatabase();
if (cred != null)
{
cred.DeleteInDatabase();
}
c.DeleteInDatabase();//no need to delete ci because of cascades
}
}
public void TestRemoteTableAttacher_Normal(DatabaseType dbType)
{
var db = GetCleanedServer(dbType);
var attacher = new RemoteTableAttacher();
//where to go for data
attacher.RemoteServer = db.Server.Name;
attacher.RemoteDatabaseName = db.GetRuntimeName();
attacher.DatabaseType = db.Server.DatabaseType;
if (db.Server.ExplicitUsernameIfAny != null)
{
var creds = new DataAccessCredentials(CatalogueRepository);
creds.Username = db.Server.ExplicitUsernameIfAny;
creds.Password = db.Server.ExplicitPasswordIfAny;
creds.SaveToDatabase();
attacher.RemoteTableAccessCredentials = creds;
}
RunAttachStageWithNormalJob(attacher, db);
}
