Login failed
VBourdages
Posts: 8
Hi,
I am trying to Data compare and synchronize 2 databases... I am able to compare, (so the login is working), but when I try to synchronize the databases I am always receiving this error message.
{"Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection." }
I am passing the password like this...
Should I pass the User and password somewhere else for the synchronization?...
The user is not part of the domain, so I am "SQL Server connection authentication", not the Windows authentication.
The error occur here...
Here the full code I am using: (please help, thanks)
Thanks[/quote]
I am trying to Data compare and synchronize 2 databases... I am able to compare, (so the login is working), but when I try to synchronize the databases I am always receiving this error message.
{"Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection." }
I am passing the password like this...
scp1=new SqlConnectionProperties(sourceServer, sourceDbName, sourceUser, sourcePw); scp2=new SqlConnectionProperties(targetServer, targetDbName, targetUser, targetPw);
Should I pass the User and password somewhere else for the synchronization?...
The user is not part of the domain, so I am "SQL Server connection authentication", not the Windows authentication.
The error occur here...
try { utils.ExecuteBlock(block, targetServer, targetDbName); } catch( Exception ex ) { System.Console.Out.Write( ex.Message ); }
Here the full code I am using: (please help, thanks)
namespace RegGateReplication { /// <summary> /// Class TablesReplication: synchronization between two databases, /// the database names and the tables list are located into the app.config file. /// </summary> public class TablesReplication { public ArrayList logList; public string sourceDbName=""; public string targetDbName=""; public string sourceServer=""; public string targetServer=""; public string SQLIntegratedSecurity=""; public string sourceUser=""; public string targetUser=""; public string sourcePw=""; public string targetPw=""; public Database db1; public Database db2; public SqlConnectionProperties scp1; public SqlConnectionProperties scp2; public string db1FieldValue=""; public string db2FieldValue=""; //Constructor public TablesReplication() { logList = new ArrayList(); sourceDbName = System.Configuration.ConfigurationSettings.AppSettings["SourceDatabase"]; targetDbName = System.Configuration.ConfigurationSettings.AppSettings["TargetDatabase"]; sourceServer = System.Configuration.ConfigurationSettings.AppSettings["SourceServer"]; targetServer = System.Configuration.ConfigurationSettings.AppSettings["TargetServer"]; SQLIntegratedSecurity = System.Configuration.ConfigurationSettings.AppSettings["IntegratedSecurity"]; sourceUser = System.Configuration.ConfigurationSettings.AppSettings["SourceUser"]; targetUser = System.Configuration.ConfigurationSettings.AppSettings["TargetUser"]; sourcePw = System.Configuration.ConfigurationSettings.AppSettings["SourcePw"]; targetPw = System.Configuration.ConfigurationSettings.AppSettings["TargetPw"]; } public void RunTablesReplication() { //Red-Gate Create Database connection... SqlProvider provider=new SqlProvider(); if (SQLIntegratedSecurity.ToUpper() == "TRUE") { scp2=new SqlConnectionProperties(targetServer, targetDbName); scp1=new SqlConnectionProperties(sourceServer, sourceDbName); scp2=new SqlConnectionProperties(targetServer, targetDbName); scp1.IntegratedSecurity = true; scp2.IntegratedSecurity = true; } else { scp1=new SqlConnectionProperties(sourceServer, sourceDbName, sourceUser, sourcePw); scp2=new SqlConnectionProperties(targetServer, targetDbName, targetUser, targetPw); scp1.IntegratedSecurity = false; scp2.IntegratedSecurity = false; } db1=provider.GetDatabase(scp1); db2=provider.GetDatabase(scp2); //Red-Gate Compare Database... ComparisonSession session=new ComparisonSession(); TableComparisonSettings settings=new TableComparisonSettings(); Tables commonTables=Tables.CreateFromIntersection(db1.Tables, db2.Tables); NameValueCollection tablelist = (NameValueCollection)ConfigurationSettings.GetConfig("Tables"); foreach(string item in tablelist.Keys) { foreach (Table table in commonTables) { if (item.ToUpper() == table.Name.ToUpper()) { settings.Add(new TableComparisonSetting(table.FullyQualifiedName, table.Fields, table.PrimaryKey.Fields)); } } } session.CompareDatabases(db1, db2, settings); //Red-Gate show Compare Database result... logList.Add("Database Compare"); logList.Add("Comparing Source DB:["+sourceServer+"]-"+sourceDbName+"with Target DB:["+targetServer+"]-"+targetDbName); logList.Add(String.Format("Starting at: {0}", DateTime.Now)); foreach (TableDifference difference in session.TableDifferences) { //set this to true so the difference will be included in the migration SQL difference.Selected=true; logList.Add(String.Format("{0}", difference.Name)); logList.Add("======="); logList.Add(String.Format("{0} identical records, {1} different records, {2} records only present in {4}, {3} records only present in {5}", difference.SameCount, difference.DifferentCount, difference.In1Count, difference.In2Count, sourceDbName, targetDbName)); //print out the different records ResultsStore differentRecords=difference.DifferentRecords; logList.Add("Different records"); logList.Add("================="); //loop through the rows for (long rowIndex=0; rowIndex<differentRecords.RowCount; rowIndex++) { logList.Add(String.Format("Row {0}", rowIndex)); logList.Add("-----"); //loop through the fields Row row=differentRecords.GetRow(rowIndex); foreach (Field field in difference.TableComparisonSetting.Fields) { if (field.OrdinalInResults1>=0) { //there are two records in the ResultsStore for this field object value1=row.Values[field.OrdinalInResults1]; object value2=row.Values[field.OrdinalInResults2]; if (value1 == null) { db1FieldValue="Null"; } else { db1FieldValue=value1.ToString(); } if (value2 == null) { db2FieldValue="Null"; } else { db2FieldValue=value1.ToString(); } if (ResultsStore.IdenticalValues(value1, value2)) { //logList.Add(String.Format("{0} {1} == {2}", field.Name, value1.ToString(), value2.ToString())); logList.Add(String.Format("{0} {1} == {2}", field.Name, db1FieldValue, db2FieldValue)); } else { //logList.Add(String.Format("{0} {1} <> {2}", field.Name, value1.ToString(), value2.ToString())); logList.Add(String.Format("{0} {1} <> {2}", field.Name, db1FieldValue, db2FieldValue)); } } else { //there is only one record in the ResultsStore //the field is part of the unique index object value=row.Values[field.OrdinalInResults]; logList.Add(String.Format("{0} ** {1}", field.Name, value.ToString())); } } } } //Begin synchronization... //we want to run the SQL on db2 so pass in true as the second parameter ExecutionBlock block=provider.GetMigrationSQL(session, true); logList.Add("Migration SQL:"); //logList.Add(block.ToString()); //Synchronize... Utils utils=new Utils(); try { utils.ExecuteBlock(block, targetServer, targetDbName); } catch( Exception ex ) { System.Console.Out.Write( ex.Message ); } logList.Add( "Ending at: " + DateTime.Now.ToString() ); // Free stuff session.Dispose(); block.Dispose(); } } }
Thanks[/quote]
This discussion has been closed.
Comments
Here the full code I am using: (please help, thanks)
namespace RegGateReplication
{
/// <summary>
/// Class TablesReplication: synchronization between two databases,
/// the database names and the tables list are located into the app.config file.
/// </summary>
public class TablesReplication
{
public ArrayList logList;
public string sourceDbName="";
public string targetDbName="";
public string sourceServer="";
public string targetServer="";
public string SQLIntegratedSecurity="";
public string sourceUser="";
public string targetUser="";
public string sourcePw="";
public string targetPw="";
public Database db1;
public Database db2;
public SqlConnectionProperties scp1;
public SqlConnectionProperties scp2;
public string db1FieldValue="";
public string db2FieldValue="";
//Constructor
public TablesReplication()
{
logList = new ArrayList();
sourceDbName = System.Configuration.ConfigurationSettings.AppSettings["SourceDatabase"];
targetDbName = System.Configuration.ConfigurationSettings.AppSettings["TargetDatabase"];
sourceServer = System.Configuration.ConfigurationSettings.AppSettings["SourceServer"];
targetServer = System.Configuration.ConfigurationSettings.AppSettings["TargetServer"];
SQLIntegratedSecurity = System.Configuration.ConfigurationSettings.AppSettings["IntegratedSecurity"];
sourceUser = System.Configuration.ConfigurationSettings.AppSettings["SourceUser"];
targetUser = System.Configuration.ConfigurationSettings.AppSettings["TargetUser"];
sourcePw = System.Configuration.ConfigurationSettings.AppSettings["SourcePw"];
targetPw = System.Configuration.ConfigurationSettings.AppSettings["TargetPw"];
}
public void RunTablesReplication()
{
//Red-Gate Create Database connection...
SqlProvider provider=new SqlProvider();
if (SQLIntegratedSecurity.ToUpper() == "TRUE")
{
scp2=new SqlConnectionProperties(targetServer, targetDbName);
scp1=new SqlConnectionProperties(sourceServer, sourceDbName);
scp2=new SqlConnectionProperties(targetServer, targetDbName);
scp1.IntegratedSecurity = true;
scp2.IntegratedSecurity = true;
}
else
{
scp1=new SqlConnectionProperties(sourceServer, sourceDbName, sourceUser, sourcePw);
scp2=new SqlConnectionProperties(targetServer, targetDbName, targetUser, targetPw);
scp1.IntegratedSecurity = false;
scp2.IntegratedSecurity = false;
}
db1=provider.GetDatabase(scp1);
db2=provider.GetDatabase(scp2);
//Red-Gate Compare Database...
ComparisonSession session=new ComparisonSession();
TableComparisonSettings settings=new TableComparisonSettings();
Tables commonTables=Tables.CreateFromIntersection(db1.Tables, db2.Tables);
NameValueCollection tablelist = (NameValueCollection)ConfigurationSettings.GetConfig("Tables");
foreach(string item in tablelist.Keys)
{
foreach (Table table in commonTables)
{
if (item.ToUpper() == table.Name.ToUpper())
{
settings.Add(new TableComparisonSetting(table.FullyQualifiedName, table.Fields, table.PrimaryKey.Fields));
}
}
}
session.CompareDatabases(db1, db2, settings);
//Red-Gate show Compare Database result...
logList.Add("Database Compare");
logList.Add("Comparing Source DB:["+sourceServer+"]-"+sourceDbName+"with Target DB:["+targetServer+"]-"+targetDbName);
logList.Add(String.Format("Starting at: {0}", DateTime.Now));
foreach (TableDifference difference in session.TableDifferences)
{
//set this to true so the difference will be included in the migration SQL
difference.Selected=true;
logList.Add(String.Format("{0}", difference.Name));
logList.Add("=======");
logList.Add(String.Format("{0} identical records, {1} different records, {2} records only present in {4}, {3} records only present in {5}", difference.SameCount, difference.DifferentCount, difference.In1Count, difference.In2Count, sourceDbName, targetDbName));
//print out the different records
ResultsStore differentRecords=difference.DifferentRecords;
logList.Add("Different records");
logList.Add("=================");
//loop through the rows
for (long rowIndex=0; rowIndex<differentRecords.RowCount; rowIndex++)
{
logList.Add(String.Format("Row {0}", rowIndex));
logList.Add("
");
//loop through the fields
Row row=differentRecords.GetRow(rowIndex);
foreach (Field field in difference.TableComparisonSetting.Fields)
{
if (field.OrdinalInResults1>=0)
{
//there are two records in the ResultsStore for this field
object value1=row.Values[field.OrdinalInResults1];
object value2=row.Values[field.OrdinalInResults2];
if (value1 == null)
{
db1FieldValue="Null";
}
else
{
db1FieldValue=value1.ToString();
}
if (value2 == null)
{
db2FieldValue="Null";
}
else
{
db2FieldValue=value1.ToString();
}
if (ResultsStore.IdenticalValues(value1, value2))
{
//logList.Add(String.Format("{0} {1} == {2}", field.Name, value1.ToString(), value2.ToString()));
logList.Add(String.Format("{0} {1} == {2}", field.Name, db1FieldValue, db2FieldValue));
}
else
{
//logList.Add(String.Format("{0} {1} <> {2}", field.Name, value1.ToString(), value2.ToString()));
logList.Add(String.Format("{0} {1} <> {2}", field.Name, db1FieldValue, db2FieldValue));
}
}
else
{
//there is only one record in the ResultsStore
//the field is part of the unique index
object value=row.Values[field.OrdinalInResults];
logList.Add(String.Format("{0} ** {1}", field.Name, value.ToString()));
}
}
}
}
//Begin synchronization...
//we want to run the SQL on db2 so pass in true as the second parameter
ExecutionBlock block=provider.GetMigrationSQL(session, true);
logList.Add("Migration SQL:");
//logList.Add(block.ToString());
//Synchronize...
Utils utils=new Utils();
try
{
utils.ExecuteBlock(block, targetServer, targetDbName);
}
catch( Exception ex )
{
System.Console.Out.Write( ex.Message );
}
logList.Add( "Ending at: " + DateTime.Now.ToString() );
// Free stuff
session.Dispose();
block.Dispose();
}
}
}
Thanks
resending the missing part...
Here it is...
//print out the different records
ResultsStore differentRecords=difference.DifferentRecords;
logList.Add("Different records");
logList.Add("=================");
//loop through the rows
for (long rowIndex=0; rowIndex<differentRecords.RowCount; rowIndex++)
{
logList.Add(String.Format("Row {0}", rowIndex));
logList.Add("
");
//loop through the fields
Row row=differentRecords.GetRow(rowIndex);
foreach (Field field in difference.TableComparisonSetting.Fields)
{
if (field.OrdinalInResults1>=0)
{
//there are two records in the ResultsStore for this field
object value1=row.Values[field.OrdinalInResults1];
object value2=row.Values[field.OrdinalInResults2];
if (value1 == null)
{
db1FieldValue="Null";
}
else
{
db1FieldValue=value1.ToString();
}
if (value2 == null)
{
db2FieldValue="Null";
}
else
{
db2FieldValue=value1.ToString();
}
if (ResultsStore.IdenticalValues(value1, value2))
{
//logList.Add(String.Format("{0} {1} == {2}", field.Name, value1.ToString(), value2.ToString()));
logList.Add(String.Format("{0} {1} == {2}", field.Name, db1FieldValue, db2FieldValue));
}
else
{
//logList.Add(String.Format("{0} {1} <> {2}", field.Name, value1.ToString(), value2.ToString()));
logList.Add(String.Format("{0} {1} <> {2}", field.Name, db1FieldValue, db2FieldValue));
}
}
else
{
//there is only one record in the ResultsStore
//the field is part of the unique index
object value=row.Values[field.OrdinalInResults];
logList.Add(String.Format("{0} ** {1}", field.Name, value.ToString()));
}
}
}
}
//Begin synchronization...
//we want to run the SQL on db2 so pass in true as the second parameter
ExecutionBlock block=provider.GetMigrationSQL(session, true);
logList.Add("Migration SQL:");
//logList.Add(block.ToString());
//Synchronize...
Utils utils=new Utils();
try
{
utils.ExecuteBlock(block, targetServer, targetDbName);
}
catch( Exception ex )
{
System.Console.Out.Write( ex.Message );
}
logList.Add( "Ending at: " + DateTime.Now.ToString() );
// Free stuff
session.Dispose();
block.Dispose();
}
}
}
Anyhow, you will probably need to give the username and password to the ExecuteBlock method the same as you did when you registered the databases:
It is working now...
Thanks.