Sql Exception: No Process Is on the Other End of the Pipe

Question

I can not access my sql server connection from c# code. I get this error:

Sql Exception: No Process Is on the Other End of the Pipe

thats the connection string in my app.config:

<add name="DefaultConnection" providerName="System.Data.SqlClient" connectionString="Data Source=BELLA\SQLEXPRESS;Initial Catalog=TLP;User Id=pascal;Password=test;Pooling=False"/>

When I use windows authentication: Integrated Security=True;

Then I can connect to the database.

BUT I can NOT use windows authentication because the opening of the sql connection is done from within a windows service which is run as LocalSystem. When I do this I get this error:

Login failed. Login failed for user 'NT AUTHORITY\SYSTEM'

Its the first time I create a login + user in sql management studio so I am nearly sure I did something wrong and its my fault.

This is what I did:

1) Create a new login in the server`s security folder with sql authentication user:pascal and password:test.

2) Went to my database and create a new user in the security folder with user: pascal and login: pascal and schema: dbo

3) Did I forget something?

Solutions from other people:

1) I have also tried this link but no luck my Sql Select on the suspect_pages table is empty.

Error: No process is on the other end of the pipe

2) My Sql Server network configuration has ENABLED on the tcp/ip, names pipes and shared memory settings.

3) SQL Server 2008 can't login with newly created user

Number 1 to 3 did not help at all.

All this is done on my local machine. No network is here.

Solution

Did you enable Shared Memory and TCP/IP providers in SQL configuration?

If not, try opening the SQL Server Configuration Manager utility and enabling Shared Memory and TCP/IP. The order that works for me is Shared Memory (1) and TCP/IP (2) for both server and client.

Also, make sure you are creating both a SQL LOGIN and DATABASE USER for PASCAL with correct rights.

Check out my blog article on creating logins. http://craftydba.com/?p=656

The snippet below will blow away and recreate your login/user with the correct default database, default schema and read/write privileges.

-- Which database to use.
USE [TLP]
GO

-- Delete existing user.
IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'pascal')
DROP USER [pascal]
GO

-- Which database to use.
USE [master]
GO


-- Delete existing login.
IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N'pascal')
DROP LOGIN [pascal]
GO

-- Add new login.
CREATE LOGIN [pascal] WITH PASSWORD=N'test', DEFAULT_DATABASE=[TLP]
GO

-- Which database to use.
USE [TLP]
GO

-- Add new user.
CREATE USER [pascal] FOR LOGIN [pascal] WITH DEFAULT_SCHEMA=[dbo]
GO

-- Add to database read / write roles
EXEC sp_addrolemember 'db_datareader', 'pascal'
EXEC sp_addrolemember 'db_datawriter', 'pascal'
GO

-- Add to database owner role?  
-- Only give out if application needs a high level of privileges.
-- EXEC sp_addrolemember 'db_owner', 'pascal'
-- GO

Server level protocols.

Client level protocols.

I never choose NETBIOS since it is a non-routable protocol.

If you are still having issues, please post a screen shot and more details.

Comments

[CRAFTY DBA]

Right click, the order option shows under client. There is not such thing for server. I do not think it is important. Since shared memory is only used if you are logged into the server. TCP/IP is used everywhere else.

[Pascal]

I did all your tips (see my init post). Then I run your script and it works. My windows service can connect and fetch data via browser :) I looked at the script but still could not find something special I have not done before via the management studio GUI. But its nice to have this script now thanks for this too.

[Niklas]

Read alot of stuff on th e net about Enabling Shared Memory while on my project my problem was actually having the wrong USER under security of sql server just because i restored a backup from a different server which had similar User as admin. so i removed and recreated the security and it worked just fine.