Prompt for username and password in Microsoft SQL Server 2000

There is a property named "Prompt", so inorder to prompt you need to first set the connection object's Prompt property to adPromptAlways.
oConn.Properties("Prompt") = adPromptAlways;

after that you can use the standards security connection sting. This will not work with Trusted Connection scenarios

Driver={SQL Server};Server=SERVERNAME;Database=DATABASENAME;uid=DATABASE USERNAME;pwd=DATABASE PASSWORD;

most people aviod using this as this is not the actual practice. Its better you do it in Standard Security manner. And remember to encrypt the password before you store in web.config file or where ever you are going to store the SQL server password.

Hey cool even OLEDB allow Prompt property

oConn.Properties("Prompt") = adPromptAlways;

It works with OLEDB also. And this also supports to connect via an IP address, but remember that the server must have a static IP inorder to perform this. Probably it must be static.
so to connect via an IP address the syntax is like

Provider=SQLOLEDB;Data Source=192.168.40.30,1433;Network Library=DBMSSOCN;Initial Catalog=DATABASENAME;User ID=DATABASE USERNAME;Password=DATABASE PASSWORD;

the port number must be given soon after the IP address Separated by a comma [,] AND remember 1433 is the default port for SQL Server 2000, and at this instant I would like to share my experience with installation. I was downloading a movie from a torrent portal and my fate the torrent client that i was using took exclusive access to the port 1433 for downloading. So when I initiated a SQL server installation it showed me some error and I knew that the port was unavailable. I looked in to the peer connection in the torrent client to see more than 500 people connected and sharing the movie. So I also need the movie to be downloaded without interruption so I continued my SQL Server 2000 installation on some other port by doing some registry hacks and trick that I found in Google research. Days passed and finally yesterday the code dint work. Only today I spotted out the problem that I dint set up the sqlserver on the default port. So its better that you avoid this approach. Its not only with the mistake I did. When a connection is associated with the port in any IP address there is a threat associated with it.

There are many port scanning software that can get all data that are transferred from the port in that IP address. So its better that you take up the standard security approach in accessing database contents rather than explicitly giving the port in the connection string.

There is also a System.Data.SQLClient namespace that is exclusively dedicated towards creating Microsoft SQL server database connections. The SqlConnection (.NET) are of 4 types this it self is the proof for customization option that is enabled by Microsoft. They are Standard Security, Trusted Connection and IP address that are the normal methods. But there is another method that is specifying the packet size. Let describe everything in a isolated manner

Standard Security

Data Source=SERVERNAME;Initial Catalog=DATABASENAME;User ID=DATABASE USERNAME;Password=DATABASE PASSWORD;

Please note that in the connection string for SqlConnection (.NET) instead of the Data Source= SERVERNAME we can also use simply Server=SERVERNAME, both produce the same results.

Trusted Connection

Data Source=SERVERNAME;Initial Catalog=DATABASENAME;Integrated Security=SSPI;

Where SSPI stands for Security Support Provider Interface, Provides a standard application interface to a number of security models.


Connect via an IP address

Data Source=1 92.168.40.30,1433;Network Library=DBMSSOCN;Initial Catalog=DATABASENAME;User ID=DATABASE USERNAME;Password=DATABASE PASSWORD;

AND FINALLY the Specifying packet size which is really the new concept in sqlconnection for .NET

Server=SERVERNAME;Database=DATABASENAME;User ID=DATABASE USERNAME;Password=DATABASE PASSWORD;Trusted_Connection=False;Packet Size=3999;

And remember any queries related to the database connection string please logon to http://www.connectionstrings.com as i too have found the website most comprehensive for connections string references. And moreover the website has more fans also.