Resolving could not open a connection to SQL Server errors

Resolving could not open a connection to SQL Server errors

Problem

Sometimes you may have issues connecting to SQL Server and you may get messages such as the following:

ERROR: (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error:) An error has occurred while establishing a connection to the server. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 5)

Or

An error has occurred 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 a connection to SQL Server) (Microsoft SQL Server, Error: 1326)

Or

A network-related error or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 – No such host is known.) (Microsoft SQL Server, Error: 11001)

These errors could be for either Named Pipes connections or TCP/IP connections. In this tip, we look at what may be causes to these errors and how to resolve.

Solution

There could be several reasons you get these error messages. Follow the below steps to see if you can resolve the issue.

Step 1

Make sure you are able to ping the physical server where SQL Server is installed from the client machine. If not, you can try to connect to the SQL Server using an IP Address (for default instance) or IP Address\Instance Name for a named instance.

If it resolves using an IP address, you can add the SQL Server machine into /etc/host file. To add the entry in the /host file type %SystemRoot%\system32\drivers\etc\ in the run window and open the host file using Notepad. In the below image I added IP address 74.200.243.253 with a machine name of SQLDBPool. Now I should be able to use the machine name instead of the IP address to connect to the SQL Server.

Step 2

Make sure the SQL services are running You can check the SQL Server services by using the SC command or SQL Server Configuration Manager. Many times you may find that the SQL Server instance is not running.

Using SQL Server Configuration Manager

Using SC command

Please note for a named instance you have to write the command as follows using the correct instance name: sc query mssql$instancename

Step 3

Check that the SQL Browser service is running. If you have installed a SQL Server named instance and not configured a specific TCP/IP port, incoming requests will be listening on a dynamic port. To resolve this you will need to have the SQL Browser service enabled and running. You can check the browser service status using Step 2 and either using SQL Server Configuration Manager or the SC command as follows.

Step 4

Make sure you are using the correct instance name. When you connect to a default instance, machinename is the best representative for the instance name and when you connect to a named instance such as sqlexpress, you should specify machinename\instancename

Step 5

Check that SQL Server is in the network. You can use the SQLCMD –L command to retrieve the list of SQL Server installed in the network. Note that this will only return SQL Servers if the SQL Browser service is running.

Step 6

Check for TCP/IP and Named Pipes protocols and port. Open SQL Server Configuration Manager and check the SQL Server Network Configuration protocols. You should enable Named Pipes and TCP/IP protocol.

For the TCP/IP protocol, right click and select properties to check the TCP/IP communication port as well. The default port is 1433, which can be changed for security purposes if needed.

Step 7

Check to see if remote connections is enabled. Right click on the server name in SSMS and select Properties. Go to the Connections tab and make sure Allow remote connection to this server is checked. If you need to make a change, you must restart the SQL Server instance to apply the change.

You can also configure the remote server connections using the below commands. If you make changes you will need to restart SQL Server for these to take affect.

The settings below are equivalent to the settings in the image above.

exec sp_configure "remote access", 1          -- 0 on, 1 off

exec sp_configure "remote query timeout", 600 -- seconds

exec sp_configure "remote proc trans", 0      -- 0 on, 1 off

Step 8

Check the error log for issues. Locally connect to SQL Server and check the error log for the port entry. You can execute XP_READERRORLOG procedure to read the errors or use SSMS. You should see entries similar to below that shows Named Pipes and TCP/IP are enabled and the port used for TCP/IP which is 1433.

Step 9

Configure the Windows Firewall for the SQL Server port and SQL Browser service. Go to Control Panel -> Click on Windows Firewall -> Go to exception tab as shown below. You can also read this tip for more information as well.

Click on Add Port… and enter the port number and name.

Click on Add Program… to add the SQL Browser service. Here you need to get the browser service executable path, normally it is located at C:\Program Files\Microsoft SQL Server\90\Shared location for SQL 2005. Browse the location and add the SQLBrowser.exe in exception list.

Step 10

If you are able to connect to SQL Server by physically logging on to the server, but unable to connect from a client computer then execute the below to check the SPN.

-- run this command to see if SPN is not found
EXEC xp_readerrorlog 0,1,"could not register the Service Principal Name",Null

If the SPN is not found, read this article and this article for more details about how to setup and register an SPN.

How Do I configure Filestream

How Do I: Use SQL File Stream

·          

Introduction

SQL Server 2008 has a file stream data type. This data type allows users to store content on the disk instead of the database as a real file.

There are many articles on this topic on the net, however they are not all from the perspective of a developer. This article will just tell us enough on how to get started removing all the extra noise.

I will try to link more advanced concepts as they are required but this article will focus on getting a developer up and running with SQL server file stream data type.

Background

The audience of this article is expected to know how to use SQL server management studio, know about authentication mechanism available in SQL server, know how to create database and so on.

There are a whole bunch of articles which explain when to use and when not to use FileStream data type. This article on MSDN is an objective one for the same.

How Do I Configure the Database Server Instance

The first thing we need to do is enable the server instance to accept file stream data type. To do this, the following steps need to be followed.

Open SQL server Configuration manager:

Right click on SQL Server Service and click on properties:

Click on FileStream tab.

Enable all the check boxes. These enable file streaming both from within SQL server and clients.

The windows share name is the name of the folder which will have the files.

Restart the service.

Open the SQL Server management studio.

Execute.

EXEC sp_configure filestream_access_level, 2

GO

RECONFIGURE

GO

The filestream_access_level 2 means that it is available for both SQL Server and from a client application. To learn more about filestream access level, please refer to this link.

How Do I Create the Database

The next step is to create a new database with file streaming enabled. There can be many ways to do this, but we will use a GUI approach.

Open SQL Server Management Studio:

Right click on databases and click on New Database.

Click on FileGroups in left navigation pane. We need to create a separate file group for the file stream.

In the FileStream, add a new file group with Name FileStream and enable default.

Click on General Pane.

Add a database name.

Click on the Add button to add another database file.

Give logical name FileSystemDB_FileStream.

Select FileType as FileStream data.

Select FileGroup as FileStream which was created in step 4.

Fill in the Path.

Click on Ok.

This now allows us to create tables with file stream.

How Do I Create the Table

In this step, we create a table in the database which will allow us to access file stream:

CREATE TABLE dbo.PictureTable

(

PkId int Primary Key IDENTITY (1, 1),

Id uniqueidentifier NOT NULL Unique ROWGUIDCOL Default newid(),

Description nvarchar(64) NOT NULL,

FileSummary varbinary(MAX),

FileData varbinary(MAX) FileStream NULL

)

The important points for this are as follows. The Table has a column Id which is of type uniqueidentifier (Guid) and is a RowGuid column. This is important because the files will be stored using this name.

The next important column is FileData which is of type VarBinary and FileStream. This will be used to stream data.

Testing using T-SQL

To test the table using TSql, we insert a row and then try to read the same:

Insert Into PictureTable([Description],[FileData])

Values('Hello World', Cast('Hello World' As varbinary(max)))

And then select using the statement

SELECT [PkId],[Id],[Description],[FileData],CAST([FileData] _

         As varchar(Max)) FROM [PictureTable]

Where are the Files Actually Stored

In case you find all the shared folders on your machine, you will see that the folder name as described in Windows share during configuration of the instance is available. However, if you will try to access this location, you will not be able to do so.

To see the file, go to the SQL Server install location and then in the data directory which will be C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA by default. In this directory, there is a folder with name FileSystemDB_FileStream which represents the table in the DB.

The table contains a file with a guid name each representing the rows stored. This is the Guid with the same value as the row guid column created in the table above.

On opening the file in a text editor, you will observe that the text HelloWorld is a part of this file.

This concludes things on the DB end and we will now proceed with how to access the file stream from .NET code in C#.

Accessing the File using .NET

We are now in the final leg of this document. This describes how to read and write data for SQL File stream using .NET code. The only two operations of interest are file reading and file writing. Both insert and Update form parts of file writing. Delete can be done using regular SQL statements used for deleting rows.

Weather we are reading data or writing data to a SqlFileStream, it has to be done in a transition. This is because in regular SQL statements, data is returned back in an atomic manner however with SqlFileStream data type the data is streamed from server to client using a buffer and hence there is no atomic operation.

SqlFileStream data type works with Integrated authentication only.

Creating a New Row

The line given below contains the code to create a single entry in the database. Let us analyse the same.

using (TransactionScope transactionScope = new TransactionScope())

{

SqlConnection sqlConnection1 = new SqlConnection("Data Source=.;

         Initial Catalog=FileSystemDB;Integrated Security=True");

SqlCommand sqlCommand1 = sqlConnection1.CreateCommand();

sqlCommand1.CommandText = "Insert Into PictureTable

         (Description,FileData) values('" + Guid.NewGuid().ToString() +

         "',Cast('' As varbinary(Max))); Select FileData.PathName()

         As Path From PictureTable Where PkId =@@Identity";

sqlConnection1.Open();

string filePath1 = (string)sqlCommand1.ExecuteScalar();

SqlConnection sqlConnection2 = new SqlConnection("Data Source=.;

         Initial Catalog=FileSystemDB;Integrated Security=True");

SqlCommand sqlCommand2 = sqlConnection2.CreateCommand();

sqlCommand2.CommandText = "Select GET_FILESTREAM_TRANSACTION_CONTEXT()

         As TransactionContext";

sqlConnection2.Open();

byte[] transactionContext1 =(byte[]) sqlCommand2.ExecuteScalar();

SqlFileStream sqlFileStream1 = new SqlFileStream

         (filePath1, transactionContext1, FileAccess.Write);

byte[] fileData = Guid.NewGuid().ToByteArray();

sqlFileStream1.Write(fileData, 0, fileData.Length);

sqlFileStream1.Close();

transactionScope.Complete();

}

The first statement is using (TransactionScope transactionScope = new TransactionScope()) which starts a transaction scope.

We next open a connection using integrated authentication and execute a query of the format:

Insert Into PictureTable (Description ,FileData ) Values(“Some String made using Guid”,

Cast(‘’ as varchar(max))

What we do here is that we create an empty file. This is done because we need the file path to upload the file.

This file path is found in the next statement using:

Select FileData.Pathname() As Path From PictureTable where PkId = @@Identity

This returns us the location of file path as a UNC share.

Next we execute the query Select GET_FILESTREAM_TRANSACTION_CONTEXT() this returns back a transaction context which corresponds to the transaction scope. It is in this transaction context that the file will be read.

Next a SqlFileStream is opened using the file path and transaction context. The code after this is a simple upload of byte array to a file stream.

Updating data will be a similar activity where we can find the file path based on some primary key.

Reading Data

Reading data is also fairly straight forward. We find the file path of a row of interest and then in a transaction scope get an instance of Transaction Context. A SqlFileStream is opened using this context and byte[] is read from the same. The code given below shows the same:

using (TransactionScope transactionScope2 = new TransactionScope())

{

SqlConnection sqlConnection3 = new SqlConnection("Data Source=.;

         Initial Catalog=FileSystemDB;Integrated Security=True");

SqlCommand sqlCommand3 = sqlConnection3.CreateCommand();

sqlCommand3.CommandText = "Select FileData.PathName() As Path,

         GET_FILESTREAM_TRANSACTION_CONTEXT() As TransactionContext

         From PictureTable Where PkId = (Select Max(PkId) From PictureTable)";

sqlConnection3.Open();

SqlDataReader reader = sqlCommand3.ExecuteReader();

reader.Read();

string filePath = (string)reader["Path"];

byte[] transactionContext2 = (byte[])reader["TransactionContext"];

SqlFileStream sqlFileStream2 = new SqlFileStream

         (filePath, transactionContext2, FileAccess.Read);

byte[] data = new byte[sqlFileStream2.Length];

sqlFileStream2.Read(data, 0, Convert.ToInt16(sqlFileStream2.Length));

Guid valueInserted = new Guid(data);

sqlFileStream2.Close();

}

Bottom of Form