SQL Server Authentication using SMO

• Download source - 20.8 KB

Introduction

In this article, I will be writing about generating a connection string by allowing the user to select a server and an instance along with database. The user could also choose between type of authentication. It works the same way SQL Server allows the user to connect to a database.

It's a simple application using SMO - SQL Server Management Objects.

Background

Ok, now I do assume that you have basic C# knowledge (Check boxes and drop-down list). Also I have used Application_Idle event and EventHandler in this project, so you will have to read about it first.

Working

I have used two objects:

1. C#

   Microsoft.SqlServer.Management.Common.ServerConnection 
   	ServerConnection m_ServerConnection;

2. C#

   Microsoft.SqlServer.Management.Smo.Server Server m_Server;

The DLLs for the above, namely Microsoft.SqlServer.Smo.dll and Microsoft.SqlServer.ConnectionInfo.dll should be present in the SQL Server directory (Program Files\Microsoft SQL Server\90\SDK\Assemblies). Make sure they are there, or else you will have to download these DLLs and include them in the references.

To start with, I have declared all the global variables in Properties.Settings. You can access it directly by opening Settings.settings file in Visual Studio IDE. There is a variable in it called BackupConnectionString which is supposed to hold the default connection string. It could be excluded if you wish so.

Now, we need to first populate the list of servers that we may have on our network. That is done in Application_Idle event. We then use that list as DataSource to the drop-down box that shows the list of all the servers available. The user should also be allowed to key in the Server name if he doesn't want to choose from the available servers:

DataTable dtServers = SmoApplication.EnumAvailableSqlServers(false);
dtServers.PrimaryKey = new DataColumn[] { dtServers.Columns[0] };
this.cmbServerName.DataSource = dtServers;

Then we need to check all the options that the user has selected, like the Authentication mode and all. On the basis of that, we change our connection string.

The user has selected the Server and the instance by now, and all the authentication details. We now need to show all the databases residing on that server when the user clicks on the Test connection button or clicks on the database drop down. However, we do ignore the system databases.

I have written a ConnectDatabase method for testing the connection:

if (!string.IsNullOrEmpty(this.cmbServerName.Text))
{
	this.m_ServerConnection = 
		new ServerConnection(this.cmbServerName.Text.ToString());
      //First check type of Authentication
      if (this.rdbWindowsAuthentication.Checked == true)   //Windows Authentication
      {
      	this.m_ServerConnection.LoginSecure = true;
            this.m_Server = new Server(this.m_ServerConnection);
      }
      else
      {
      	// Create a new connection to the selected server name
		this.m_ServerConnection.LoginSecure = false;
      	this.m_ServerConnection.Login = this.txtUserName.Text;       //Login User
	//Login Password
         this.m_ServerConnection.Password = this.txtPassword.Text;    
	this.m_ServerConnection.DatabaseName = this.cmbDbName.Text;  //Database Name
      	// Create a new SQL Server object using the connection we created
      	this.m_Server = new Server(this.m_ServerConnection);
      }
	return true;
}
return false;

To add the name of databases to the dropdown box:

this.cmbDbName.Items.Clear();
// Loop through the databases list
foreach (Database db in this.m_Server.Databases)
{
      //We don't want to be adding the System databases to our list
      //Check if database is system database
      if (!db.IsSystemObject) 
      {
 	     this.cmbDbName.Items.Add(db.Name); // Add database to combobox
      }
}
this.cmbDbName.SelectedIndex = 0;

When the user presses the Ok button, I add all the values to the global variables declared in Properties.Settings.

History

• 17^th December, 2008: Initial post