Wednesday, December 17, 2008

How to connect SQL SERVER instance ?

This article is for those who are new to SQL Server or for those who are new to windows platform.

Quiet a lot of people go to Google and search for these key words “SQL Server”+FREE. Top in the results listed is the Microsoft SQL Server Express edition. Yes- Microsoft SQL Server Express edition is free to user and it comes with certain limitations please read the documentation before you start using it. Curious to learn the SQL Server quickly most of us download and install it. Next is how do I connect and start writing and testing some queries, as it does not come with standard client connectivity GUI tools

There are two methods of user authentication in SQL Server unlike most of the RDBMS products like Oracle, MySQL, Sybase etc. The two authentication methods are know are

Window Authentication and

SQL Server Authentication

In case of “Window Authentication” your connection request is authenticated against the windows operating system. if you are using SQL Server Management studio or another other GUI’s tools and if you are the local admin of your system (which mostly we are all) you need to make following selections in your connection screen >> SQL Server Name and Authentication = “Window Authentication” (or integrated authentication in some cases) no need of password anywhere.

If you are using OSQL or SQLCMD – the command line client utility to manage SQL Server, which is mostly installed when you install SQL Server (any edition). You just need to type following command at command prompt

{Third party GUI Tools}

osql -E -S computer_name\instance_name

Where computer_name = your desktop/pc name, no need of keying in any PASSWORD as you have specified –E option i.e. to use window authentication. If you (logged in user) are a valid windows user and have a local admin privileges you will be successfully connecting to SQL Server instance.

In case of “SQL Server Authentication” your connection request is authenticated by the SQL Server itself. In this case you need to pass one more value i.e. password along with user name and also you need to opt for “SQL Server authentication” if there is any in you GUI tool. In this case the OSQL command would be,

osql -S computer_name\instance_name –U <SQL_userId> -P <respective password>

{command line utils}

No comments:

About Me

By profession I am a Database Administrator (DBA) with total 13 yrs. of experience in the field of Information Technology, out of that 9 yrs as SQL DBA and last 3 years in IBM System i/iSeries and DB2 LUW 9. I have handled Developer, plus production support roles, and I like both the roles. I love and live information technology hence the name "Techonologyyogi" Apart from that I am a small, retail investor, with small investments in India and United States in the form of Equity holdings via common stocks. Don't ask me if I have made money, I have been loosing money in stocks.