Click here to Skip to main content
13,293,178 members (85,733 online)
Click here to Skip to main content
Add your own
alternative version


31 bookmarked
Posted 17 Dec 2008

SQL Server Authentication using SMO

, 17 Dec 2008
Rate this:
Please Sign up or sign in to vote.
Setup a connection string by allowing the user to select the server and database


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.


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.


I have used two objects:

  1. Microsoft.SqlServer.Management.Common.ServerConnection 
    	ServerConnection m_ServerConnection;
  2. 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);
      	// 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:

// 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.


  • 17th December, 2008: Initial post


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Rupesh Tarwade
Technical Lead
India India
I have been designing and developing systems in .NET since 2006. Started out with .NET 1.1, and progressed through to current version. Started working with SharePoint 2010 in 2011, and am currently gaining experience in SharePoint Online and hosted apps.

You may also be interested in...


Comments and Discussions

GeneralMy vote of 5 Pin
Lambin13-Dec-10 5:47
memberLambin13-Dec-10 5:47 
GeneralRe: My vote of 5 Pin
Rupesh Tarwade30-May-11 21:47
memberRupesh Tarwade30-May-11 21:47 
GeneralA little suggestion about your code Pin
Lambin13-Dec-10 5:43
memberLambin13-Dec-10 5:43 
QuestionWhy yellow "Gathering information" message showing - very long time? Pin
gramir2-Aug-10 2:41
membergramir2-Aug-10 2:41 
AnswerRe: Why yellow "Gathering information" message showing - very long time? Pin
Lambin13-Dec-10 5:17
memberLambin13-Dec-10 5:17 
GeneralCrash Pin
rctaubert8-May-09 4:50
memberrctaubert8-May-09 4:50 
QuestionGreat Article but.... Pin
RighteousBrother9-Jan-09 6:16
memberRighteousBrother9-Jan-09 6:16 
AnswerRe: Great Article but.... Pin
Rupesh Tarwade11-Jan-09 21:01
memberRupesh Tarwade11-Jan-09 21:01 
GeneralRe: Great Article but.... Pin
RighteousBrother11-Jan-09 23:34
memberRighteousBrother11-Jan-09 23:34 
GeneralReusing an existing dialog Pin
Artem Smirnov23-Dec-08 10:13
memberArtem Smirnov23-Dec-08 10:13 
GeneralRe: Reusing an existing dialog Pin
Rupesh Tarwade23-Dec-08 22:13
memberRupesh Tarwade23-Dec-08 22:13 
GeneralRe: Reusing an existing dialog Pin
Member 429826510-Mar-10 23:44
memberMember 429826510-Mar-10 23:44 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.171207.1 | Last Updated 17 Dec 2008
Article Copyright 2008 by Rupesh Tarwade
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid