Connection

Configure SQL Server to allow Remote Connections

Configure SQL Server to allow Remote Connections
Image by tookapic from Pixabay
Data Masker is the easiest to use and most cost effective SQL Server data masking tool available.

KEY FEATURES

A summary of the Data Masker software.

DATA MASKING: WHAT YOU NEED TO KNOW

A comprehensive survey of the techniques and issues you need to know about before you begin data masking.

DOWNLOAD DATA MASKER

A 30 day, fully functional, evaluation copy of the Data Masker software.

QUICK START

A Quick start to the Data Masker software.

DATA MASKER FAQ

Answers to frequently asked questions about the Data Masker software.

SYSTEM REQUIREMENTS

The hardware & software supported by Data Masker.

Errors connecting to SQL Server Express 2005

Are you getting errors connecting to SQL Server Express 2005 via remote client software and yet have no problem connecting to it on the local machine?

Some of the errors you might be seeing are:

  • sql server does not allow remote connections
  • SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified
  • An error has occured while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.(provider:Named Pipes Provider, error:40-Could not open connection to SQL Server))
  • Server does not exist or access denied

If yes, then read on, because SQL Server Express 2005 is not automatically configured for remote access during installation. It can be enabled however, so the problems you are having are more of a “feature” than a bug.

Why this page? Well, we too ran into this problem – just as you have now. In recognition of all of the kind help we received from the Internet newsgroups we thought it might be useful to collect the information here so as to provide assistance to others in the same position. If you have any suggestions for updates to this page please ] Make sure the SQL Server browser is started. Note this step is optional. It is possible to set the SQL Server instance to use a fixed IP address – but this is non-standard for named instances. See sqlexpress’s WebLog for details.

  • Make sure SQL Server and SQL Server Browser are exempted by the firewall on the server machine. This is done by putting sqlservr.exe and sqlbrowser.exe as an exception in the windows firewall. Chris D. sent in a note which might help.
  • Note: In order to get things to work. You might need to completely reboot the server machine after making the changes. There have been reports (thanks P.C.) that starting and stopping the SQL Server and Browser software is not enough.

Client software for testing remote connectivity to SQL Server Express 2005

Need some software to test out remote connectivity to SQL Server Express 2005? Probably there are lots of ways. Here are four to try out:

  • Download and install the SQL Server Management Studio Express Community Technical Preview. SSMSECTP (as it is affectionately known) is a freebie, upgraded and cut down version of the SQL Server 2000 management software. Works pretty well for simple management tasks, has a T-SQL window and, of course, nicely tests out remote connectivity.

To install SSMSE you will need the following (note the 2.0 Beta version of the .NET framework is not acceptable. Uninstall it first if you have it.). It is probably best to install them in the order below

Microsoft .NET Framework Version 2.0 Redistributable Package Microsoft Core XML Services (MSXML) 6.0 Microsoft SQL Server Management Studio Express – Community Technology Preview (CTP) November 2005

  • Or try the ADO bare bones Connection tip using .udl files sent in by Kieran H and Dan P.
  • Or try the client side osql.exe utility with the following command line:

osql -S SERVERNAMESQLEXPRESS -U

To use OSQL, start up a Windows command session (Start:Run…:cmd) and type in the above command. On our test machine the OSQL binaries are located in the C:Program FilesMicrosoft SQL Server90ToolsBinn directory. If you do not have this directory somewhere it probably means you did not install the client side tools.

So of course now the question is how to install the OSQL tool (and others) on the client” Well fortunately it is not too difficult as they are co-located in the same installation package as the SQL Server Express 2005 software. To install them just go back to the same installer you used to create the SQL Server Express 2005 instance on the remote system and run it on your client system. Instead of accepting the default install (which installs the server engine) choose to install only the client side utilities. This will create the above directory and install the tools without installing the database on your client machine.

For further testing you can also dig around a bit and find out how to use the SQLCMD.exe utility.

  • Or use the following snippet of C# code. Note: If you are using the Visual Studio 2005 environment you might want to read the page entitled Using Visual Studio 2005 to Connect to SQL Servers by Name

private void button1_Click(object sender, System.EventArgs e) { SqlDataReader rdr = null; // 1. Instantiate the connection // test by server name instance name // SqlConnection conn = new SqlConnection( // “Data Source=MYSERVERNAMESQLEXPRESS; // Initial Catalog=Northwind; // User ID=sa; // Password=sa”); // test by ip address, port and instance name SqlConnection conn = new SqlConnection( “Data Source=123.123.123.123, 1066; Network Library=DBMSSOCN; Initial Catalog=Northwind; User ID=sa;Password=sa;”); // 2. Open the connection conn.Open; // 3. Pass the connection to a command object SqlCommand cmd = new SqlCommand( “select * from Customers”, conn); // 4. Use the connection rdr = cmd.ExecuteReader; // print the CustomerID of each record while (rdr.Read) { Console.WriteLine(rdr[0]); } // close the reader if (rdr != null) { rdr.Close; // 5. Close the connection if (conn != null) { conn.Close; } MessageBox.Show(“Done”, “Done”, MessageBoxButtons.OK, MessageBoxIcon.Exclamation); }

Other resources

SQL Server 2005 Surface Configuration Tool article by Mike Gunderloy.

Source: www.datamasker.com