private void tabControl_main_SelectedIndexChanged(object sender, EventArgs e)
{
if (this.tabControl_main.SelectedTab == this.tabPage_createLogin)
{
MsSqlServerInfo info = null;
string strError = "";
// 获得 SQL Server 信息
// return:
// -1 出错
// 0 放弃
// 1 成功
int nRet = GetSqlServerInfo(
this.textBox_sqlServerName.Text,
out info,
out strError);
if (nRet == -1)
{
MessageBox.Show(this, strError);
return;
}
if (nRet == 0)
{
return;
}
this._sqlServerInfo = info;
this.button_copySqlServerInfo.Enabled = true;
if (info.IntegratedSecurityOnlyMode == false &&
info.IsLocalDB() == false)
{
if (string.IsNullOrEmpty(this.textBox_loginName.Text) == true)
{
this.textBox_loginName.Text = this.textBox_instanceName.Text;
}
if (string.IsNullOrEmpty(this.textBox_loginName.Text) == true)
{
this.textBox_loginName.Text = "dp2kernel"; // 缺省的名字
}
}
else // if (info.IntegratedSecurityOnlyMode == true)
{
this.textBox_loginName.Text = "";
this.textBox_loginPassword.Text = "";
this.textBox_confirmLoginPassword.Text = "";
this.groupBox_login.Enabled = false;
}
this.DebugInfo = DateTime.Now.ToString() + "\r\n" + info.GetSummary() + "\r\n\r\n";
}
}
private void textBox_sqlServerName_TextChanged(object sender, EventArgs e)
{
this._sqlServerInfo = null;
this.button_copySqlServerInfo.Enabled = false;
}
// 创建一个适合于dp2Kernel的SQL Server login
// return:
// -1 出错
// 0 成功
// 1 原来已经存在，且不允许删除
int CreateLogin(
MsSqlServerInfo info,
string strLoginName,
string strLoginPassword,
out string strError)
{
strError = "";
string strConnection = @"Persist Security Info=False;"
+ "User ID=" + info.SqlUserName + ";" //帐户和密码
+ "Password="******";"
+ "Data Source=" + info.ServerName + ";"
+ "Connect Timeout=30";
if (info.SSPI == true)
{
strConnection = @"Persist Security Info=False;"
+ "Integrated Security=SSPI; " //信任连接
+ "Data Source=" + info.ServerName + ";"
+ "Connect Timeout=30"; // 30秒
}
SqlConnection connection = null;
try
{
connection = new SqlConnection(strConnection);
}
catch (Exception ex)
{
strError = "建立连接出错：" + ex.Message + " 类型:" + ex.GetType().ToString();
return(-1);
}
try
{
connection.Open();
}
catch (SqlException sqlEx)
{
strError = "连接 SQL 服务器时出错：" + sqlEx.Message + "。";
int nError = sqlEx.ErrorCode;
return(-1);
}
catch (Exception ex)
{
strError = "连接 SQL 服务器时出错：" + ex.Message + " 类型:" + ex.GetType().ToString();
return(-1);
}
/* http://support.microsoft.com/kb/321185
* 10 -- SQL Server 2008
* 9 -- SQL Server 2005
* 8 -- SQL 2000
* 7 -- SQL 7.0
* */
// 先删除同名的login
// strCommand = "IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'" + strLoginName + "') DROP LOGIN [" + strLoginName + "]"; // sql 2005
try
{
string strCommand = "";
SqlCommand command = null;
if (info.Version == "10")
{
strCommand = "DROP LOGIN [" + strLoginName + "]";
}
else
{
strCommand = "EXEC sp_droplogin @loginame = '" + strLoginName + "'"; // sql 2000
}
command = new SqlCommand(strCommand,
connection);
try
{
command.ExecuteNonQuery();
}
catch (SqlException ex)
{
// 需要判断SqlException的具体类型，针对性地报错或者不报错
if (IsSqlErrorNo(ex, 15007) == true)
{
// 15007 The login '%s' does not exist.
}
else if (IsSqlErrorNo(ex, 15151) == true)
{
// ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.zh-CHS/s10de_5techref/html/e9f7b86b-891e-4abb-938e-c39c707f5a5f.htm
// 15151 无法对 %S_MSG '%.*ls' 执行 %S_MSG，因为它不存在，或者您没有所需的权限。
}
else if (IsSqlErrorNo(ex, 15174) == true)
{
// 15174 16 登录 ''%1!'' 拥有一个或多个数据库。请更改下列数据库的所有者后再除去该登录：
strError = "警告：执行命令 " + strCommand + " 出错：" + ex.Message + " 类型：" + ex.GetType().ToString();
return(1);
}
else
{
strError = "警告：执行命令 " + strCommand + " 出错：" + ex.Message + " 类型：" + ex.GetType().ToString();
MessageBox.Show(this, strError);
}
}
catch (Exception ex)
{
strError = "警告：执行命令 " + strCommand + " 出错：" + ex.Message + " 类型：" + ex.GetType().ToString();
MessageBox.Show(this, strError);
}
if (info.Version == "10")
{
strCommand = "CREATE LOGIN [" + strLoginName + "] WITH PASSWORD=N'" + strLoginPassword + "', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF";
}
else
{
strCommand = "EXEC sp_addlogin @loginame='" + strLoginName + "', @passwd='" + strLoginPassword + "', @defdb='master'";
}
command = new SqlCommand(strCommand,
connection);
try
{
command.ExecuteNonQuery();
}
catch (Exception ex)
{
strError = "执行命令 " + strCommand + " 出错：" + ex.Message + " 类型：" + ex.GetType().ToString();
return(-1);
}
strCommand = "EXEC sp_addsrvrolemember @loginame = '" + strLoginName + "', @rolename = 'dbcreator'";
command = new SqlCommand(strCommand,
connection);
try
{
command.ExecuteNonQuery();
}
catch (Exception ex)
{
strError = "执行命令 " + strCommand + " 出错：" + ex.Message + " 类型：" + ex.GetType().ToString();
return(-1);
}
}
finally
{
connection.Close();
}
return(0);
}
int AddSystemDbCreatorRole(MsSqlServerInfo info,
out string strError)
{
strError = "";
string strConnection = @"Persist Security Info=False;"
+ "User ID=" + info.SqlUserName + ";" //帐户和密码
+ "Password="******";"
+ "Data Source=" + info.ServerName + ";"
+ "Connect Timeout=30";
if (info.SSPI == true)
{
strConnection = @"Persist Security Info=False;"
+ "Integrated Security=SSPI; " //信任连接
+ "Data Source=" + info.ServerName + ";"
+ "Connect Timeout=30"; // 30秒
}
SqlConnection connection = null;
try
{
connection = new SqlConnection(strConnection);
}
catch (Exception ex)
{
strError = "建立连接出错：" + ex.Message + " 类型:" + ex.GetType().ToString();
return(-1);
}
try
{
connection.Open();
}
catch (SqlException sqlEx)
{
strError = "连接SQL服务器出错：" + sqlEx.Message + "。";
int nError = sqlEx.ErrorCode;
return(-1);
}
catch (Exception ex)
{
strError = "连接SQL服务器出错：" + ex.Message + " 类型:" + ex.GetType().ToString();
return(-1);
}
/* http://support.microsoft.com/kb/321185
* 10 -- SQL Server 2008
* 9 -- SQL Server 2005
* 8 -- SQL 2000
* 7 -- SQL 7.0
* */
try
{
string strCommand = "";
SqlCommand command = null;
strCommand = "EXEC sp_addsrvrolemember @loginame = 'NT AUTHORITY\\SYSTEM', @rolename = 'dbcreator'";
command = new SqlCommand(strCommand,
connection);
try
{
command.ExecuteNonQuery();
}
catch (Exception ex)
{
strError = "执行命令 " + strCommand + " 出错：" + ex.Message + " 类型：" + ex.GetType().ToString();
return(-1);
}
}
finally
{
connection.Close();
}
return(0);
}
// 获得 SQL Server 信息
// return:
// -1 出错
// 0 放弃
// 1 成功
int GetSqlServerInfo(
string strSqlServerName,
out MsSqlServerInfo info,
out string strError)
{
strError = "";
int nRet = 0;
info = new MsSqlServerInfo();
REDO_INPUT:
SaLoginDialog dlg = new SaLoginDialog();
GuiUtil.AutoSetDefaultFont(dlg);
dlg.SqlServerName = strSqlServerName;
dlg.StartPosition = FormStartPosition.CenterScreen;
dlg.ShowDialog(this);
if (dlg.DialogResult != DialogResult.OK)
{
return(0);
}
info.ServerName = strSqlServerName;
info.SqlUserName = dlg.SqlUserName;
info.SqlUserPassword = dlg.SqlPassword;
info.SSPI = dlg.SSPI;
string strConnection = @"Persist Security Info=False;"
+ "User ID=" + info.SqlUserName + ";" //帐户和密码
+ "Password="******";"
+ "Data Source=" + strSqlServerName + ";"
+ "Connect Timeout=30";
if (info.SSPI == true)
{
strConnection = @"Persist Security Info=False;"
+ "Integrated Security=SSPI; " //信任连接
+ "Data Source=" + strSqlServerName + ";"
+ "Connect Timeout=30"; // 30秒
}
SqlConnection connection = null;
try
{
connection = new SqlConnection(strConnection);
}
catch (Exception ex)
{
strError = "建立连接时出错：" + ex.Message + " 类型:" + ex.GetType().ToString();
return(-1);
}
try
{
connection.Open();
}
catch (SqlException sqlEx)
{
strError = "连接SQL服务器出错：" + sqlEx.Message + "。";
int nError = sqlEx.ErrorCode;
MessageBox.Show(this, strError);
goto REDO_INPUT;
return(-1);
}
catch (Exception ex)
{
strError = "连接SQL服务器出错：" + ex.Message + " 类型:" + ex.GetType().ToString();
return(-1);
}
/* http://support.microsoft.com/kb/321185
* 10 -- SQL Server 2008
* 9 -- SQL Server 2005
* 8 -- SQL 2000
* 7 -- SQL 7.0
* */
try
{
string strVersion = "7";
string strCommand = "";
SqlCommand command = null;
// Debug.Assert(false, "");
strCommand = "SELECT SERVERPROPERTY('productversion')";
command = new SqlCommand(strCommand,
connection);
try
{
strVersion = (string)command.ExecuteScalar();
// 去掉次要版本号
nRet = strVersion.IndexOf(".");
if (nRet != -1)
{
strVersion = strVersion.Substring(0, nRet);
}
}
catch (Exception /*ex*/)
{
// strError = "执行命令 " + strCommand + " 出错：" + ex.Message + " 类型：" + ex.GetType().ToString();
// return -1;
strVersion = "7";
}
info.Version = strVersion;
strCommand = "SELECT SERVERPROPERTY('IsIntegratedSecurityOnly')";
command = new SqlCommand(strCommand,
connection);
try
{
nRet = (Int32)command.ExecuteScalar();
}
catch (Exception ex)
{
//strError = "执行命令 " + strCommand + " 出错：" + ex.Message + " 类型：" + ex.GetType().ToString();
//return -1;
nRet = 1;
}
if (nRet == 1)
{
info.IntegratedSecurityOnlyMode = true;
}
else
{
info.IntegratedSecurityOnlyMode = false;
}
}
finally
{
connection.Close();
}
return(1);
}
private void textBox_sqlServerName_TextChanged(object sender, EventArgs e)
{
this._sqlServerInfo = null;
this.button_copySqlServerInfo.Enabled = false;
}
// 创建一个适合于dp2Kernel的SQL Server login
// return:
// -1 出错
// 0 成功
// 1 原来已经存在，且不允许删除
int CreateLogin(
MsSqlServerInfo info,
string strLoginName,
string strLoginPassword,
out string strError)
{
strError = "";
string strConnection = @"Persist Security Info=False;"
+ "User ID=" + info.SqlUserName + ";" //帐户和密码
+ "Password="******";"
+ "Data Source=" + info.ServerName + ";"
+ "Connect Timeout=30";
if (info.SSPI == true)
{
strConnection = @"Persist Security Info=False;"
+ "Integrated Security=SSPI; " //信任连接
+ "Data Source=" + info.ServerName + ";"
+ "Connect Timeout=30"; // 30秒
}
SqlConnection connection = null;
try
{
connection = new SqlConnection(strConnection);
}
catch (Exception ex)
{
strError = "建立连接出错：" + ex.Message + " 类型:" + ex.GetType().ToString();
return -1;
}
try
{
connection.Open();
}
catch (SqlException sqlEx)
{
strError = "连接 SQL 服务器时出错：" + sqlEx.Message + "。";
int nError = sqlEx.ErrorCode;
return -1;
}
catch (Exception ex)
{
strError = "连接 SQL 服务器时出错：" + ex.Message + " 类型:" + ex.GetType().ToString();
return -1;
}
/* http://support.microsoft.com/kb/321185
* 10 -- SQL Server 2008
* 9 -- SQL Server 2005
* 8 -- SQL 2000
* 7 -- SQL 7.0
* */
// 先删除同名的login
// strCommand = "IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'" + strLoginName + "') DROP LOGIN [" + strLoginName + "]"; // sql 2005
try
{
string strCommand = "";
SqlCommand command = null;
if (info.Version == "10")
strCommand = "DROP LOGIN [" + strLoginName + "]";
else
strCommand = "EXEC sp_droplogin @loginame = '" + strLoginName + "'"; // sql 2000
command = new SqlCommand(strCommand,
connection);
try
{
command.ExecuteNonQuery();
}
catch (SqlException ex)
{
// 需要判断SqlException的具体类型，针对性地报错或者不报错
if (IsSqlErrorNo(ex, 15007) == true)
{
// 15007 The login '%s' does not exist.
}
else if (IsSqlErrorNo(ex, 15151) == true)
{
// ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.zh-CHS/s10de_5techref/html/e9f7b86b-891e-4abb-938e-c39c707f5a5f.htm
// 15151 无法对 %S_MSG '%.*ls' 执行 %S_MSG，因为它不存在，或者您没有所需的权限。
}
else if (IsSqlErrorNo(ex, 15174) == true)
{
// 15174 16 登录 ''%1!'' 拥有一个或多个数据库。请更改下列数据库的所有者后再除去该登录：
strError = "警告：执行命令 " + strCommand + " 出错：" + ex.Message + " 类型：" + ex.GetType().ToString();
return 1;
}
else
{
strError = "警告：执行命令 " + strCommand + " 出错：" + ex.Message + " 类型：" + ex.GetType().ToString();
MessageBox.Show(this, strError);
}
}
catch (Exception ex)
{
strError = "警告：执行命令 " + strCommand + " 出错：" + ex.Message + " 类型：" + ex.GetType().ToString();
MessageBox.Show(this, strError);
}
if (info.Version == "10")
strCommand = "CREATE LOGIN [" + strLoginName + "] WITH PASSWORD=N'" + strLoginPassword + "', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF";
else
strCommand = "EXEC sp_addlogin @loginame='" + strLoginName + "', @passwd='" + strLoginPassword + "', @defdb='master'";
command = new SqlCommand(strCommand,
connection);
try
{
command.ExecuteNonQuery();
}
catch (Exception ex)
{
strError = "执行命令 " + strCommand + " 出错：" + ex.Message + " 类型：" + ex.GetType().ToString();
return -1;
}
strCommand = "EXEC sp_addsrvrolemember @loginame = '" + strLoginName + "', @rolename = 'dbcreator'";
command = new SqlCommand(strCommand,
connection);
try
{
command.ExecuteNonQuery();
}
catch (Exception ex)
{
strError = "执行命令 " + strCommand + " 出错：" + ex.Message + " 类型：" + ex.GetType().ToString();
return -1;
}
}
finally
{
connection.Close();
}
return 0;
}
int AddSystemDbCreatorRole(MsSqlServerInfo info,
out string strError)
{
strError = "";
string strConnection = @"Persist Security Info=False;"
+ "User ID=" + info.SqlUserName + ";" //帐户和密码
+ "Password="******";"
+ "Data Source=" + info.ServerName + ";"
+ "Connect Timeout=30";
if (info.SSPI == true)
{
strConnection = @"Persist Security Info=False;"
+ "Integrated Security=SSPI; " //信任连接
+ "Data Source=" + info.ServerName + ";"
+ "Connect Timeout=30"; // 30秒
}
SqlConnection connection = null;
try
{
connection = new SqlConnection(strConnection);
}
catch (Exception ex)
{
strError = "建立连接出错：" + ex.Message + " 类型:" + ex.GetType().ToString();
return -1;
}
try
{
connection.Open();
}
catch (SqlException sqlEx)
{
strError = "连接SQL服务器出错：" + sqlEx.Message + "。";
int nError = sqlEx.ErrorCode;
return -1;
}
catch (Exception ex)
{
strError = "连接SQL服务器出错：" + ex.Message + " 类型:" + ex.GetType().ToString();
return -1;
}
/* http://support.microsoft.com/kb/321185
* 10 -- SQL Server 2008
* 9 -- SQL Server 2005
* 8 -- SQL 2000
* 7 -- SQL 7.0
* */
try
{
string strCommand = "";
SqlCommand command = null;
strCommand = "EXEC sp_addsrvrolemember @loginame = 'NT AUTHORITY\\SYSTEM', @rolename = 'dbcreator'";
command = new SqlCommand(strCommand,
connection);
try
{
command.ExecuteNonQuery();
}
catch (Exception ex)
{
strError = "执行命令 " + strCommand + " 出错：" + ex.Message + " 类型：" + ex.GetType().ToString();
return -1;
}
}
finally
{
connection.Close();
}
return 0;
}
// 获得 SQL Server 信息
// return:
// -1 出错
// 0 放弃
// 1 成功
int GetSqlServerInfo(
string strSqlServerName,
out MsSqlServerInfo info,
out string strError)
{
strError = "";
int nRet = 0;
info = new MsSqlServerInfo();
REDO_INPUT:
SaLoginDialog dlg = new SaLoginDialog();
GuiUtil.AutoSetDefaultFont(dlg);
dlg.SqlServerName = strSqlServerName;
dlg.StartPosition = FormStartPosition.CenterScreen;
dlg.ShowDialog(this);
if (dlg.DialogResult != DialogResult.OK)
return 0;
info.ServerName = strSqlServerName;
info.SqlUserName = dlg.SqlUserName;
info.SqlUserPassword = dlg.SqlPassword;
info.SSPI = dlg.SSPI;
string strConnection = @"Persist Security Info=False;"
+ "User ID=" + info.SqlUserName + ";" //帐户和密码
+ "Password="******";"
+ "Data Source=" + strSqlServerName + ";"
+ "Connect Timeout=30";
if (info.SSPI == true)
{
strConnection = @"Persist Security Info=False;"
+ "Integrated Security=SSPI; " //信任连接
+ "Data Source=" + strSqlServerName + ";"
+ "Connect Timeout=30"; // 30秒
}
SqlConnection connection = null;
try
{
connection = new SqlConnection(strConnection);
}
catch (Exception ex)
{
strError = "建立连接时出错：" + ex.Message + " 类型:" + ex.GetType().ToString();
return -1;
}
try
{
connection.Open();
}
catch (SqlException sqlEx)
{
strError = "连接SQL服务器出错：" + sqlEx.Message + "。";
int nError = sqlEx.ErrorCode;
MessageBox.Show(this, strError);
goto REDO_INPUT;
return -1;
}
catch (Exception ex)
{
strError = "连接SQL服务器出错：" + ex.Message + " 类型:" + ex.GetType().ToString();
return -1;
}
/* http://support.microsoft.com/kb/321185
* 10 -- SQL Server 2008
* 9 -- SQL Server 2005
* 8 -- SQL 2000
* 7 -- SQL 7.0
* */
try
{
string strVersion = "7";
string strCommand = "";
SqlCommand command = null;
// Debug.Assert(false, "");
strCommand = "SELECT SERVERPROPERTY('productversion')";
command = new SqlCommand(strCommand,
connection);
try
{
strVersion = (string)command.ExecuteScalar();
// 去掉次要版本号
nRet = strVersion.IndexOf(".");
if (nRet != -1)
strVersion = strVersion.Substring(0, nRet);
}
catch (Exception /*ex*/)
{
// strError = "执行命令 " + strCommand + " 出错：" + ex.Message + " 类型：" + ex.GetType().ToString();
// return -1;
strVersion = "7";
}
info.Version = strVersion;
strCommand = "SELECT SERVERPROPERTY('IsIntegratedSecurityOnly')";
command = new SqlCommand(strCommand,
connection);
try
{
nRet = (Int32)command.ExecuteScalar();
}
catch (Exception ex)
{
//strError = "执行命令 " + strCommand + " 出错：" + ex.Message + " 类型：" + ex.GetType().ToString();
//return -1;
nRet = 1;
}
if (nRet == 1)
info.IntegratedSecurityOnlyMode = true;
else
info.IntegratedSecurityOnlyMode = false;
}
finally
{
connection.Close();
}
return 1;
}
private void tabControl_main_SelectedIndexChanged(object sender, EventArgs e)
{
if (this.tabControl_main.SelectedTab == this.tabPage_createLogin)
{
MsSqlServerInfo info = null;
string strError = "";
// 获得 SQL Server 信息
// return:
// -1 出错
// 0 放弃
// 1 成功
int nRet = GetSqlServerInfo(
this.textBox_sqlServerName.Text,
out info,
out strError);
if (nRet == -1)
{
MessageBox.Show(this, strError);
return;
}
if (nRet == 0)
return;
this._sqlServerInfo = info;
this.button_copySqlServerInfo.Enabled = true;
if (info.IntegratedSecurityOnlyMode == false
&& info.IsLocalDB() == false)
{
if (string.IsNullOrEmpty(this.textBox_loginName.Text) == true)
this.textBox_loginName.Text = this.textBox_instanceName.Text;
if (string.IsNullOrEmpty(this.textBox_loginName.Text) == true)
this.textBox_loginName.Text = "dp2kernel"; // 缺省的名字
}
else // if (info.IntegratedSecurityOnlyMode == true)
{
this.textBox_loginName.Text = "";
this.textBox_loginPassword.Text = "";
this.textBox_confirmLoginPassword.Text = "";
this.groupBox_login.Enabled = false;
}
this.DebugInfo = DateTime.Now.ToString() + "\r\n" + info.GetSummary() + "\r\n\r\n";
}
}
