Problem to login

Hi,
How to resolve this problem, to login the database?

TITLE: Connect to Server
------------------------------

Cannot connect to PC326VM01\SSEXPR12.

------------------------------
ADDITIONAL INFORMATION:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=233&LinkId=20476

------------------------------

No process is on the other end of the pipe

------------------------------
BUTTONS:

OK
------------------------------

Open in new window

I'm actually connecting to Sql server, within the same machine.
LVL 12
HuaMin ChenProblem resolverAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

arnoldCommented:
It is one thing connecting to the sql server while connected to it, versus connecting to the sql server from the network.

Double check the sql server configuration to make sure tcp/ip is one of the established protocols ough which a connection can be made.

Try telnet to connect on port 1433 which is commonly the default sql port.

Which sql server are you using, sql 2005? Sql configuration for server you need to enable both shared memory, pipe and tcp/ip protocols.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HuaMin ChenProblem resolverAuthor Commented:
I'm using Sql server 2012 express within Win 7 machine.

Sorry, I get this
C:\Users\zcwm>telnet 1433
Connecting To 1433...Could not open connection to the host, on port 23: Connect
failed

you need to enable both shared memory, pipe and tcp/ip protocols.
Can I have more details to this?

Even after I've already enabled TCP/IP, and have put 1433 as TCP port (then have rebooted the machine), I now still get this

Cannot connect to PC326VM01\SSEXPR12.

------------------------------
ADDITIONAL INFORMATION:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
you need to enable both shared memory, pipe and tcp/ip protocols
Did you enabled all these protocols? Not only TCP/IP but all.
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
This looks like an authentication problem rather than a connection problem.  How do you connect AD or SQL login ?
0
arnoldCommented:
The error deals with a lack of an appropriate connection option.

I.e. The client is only allowed named pipes and shared memory, while the server only allowed shared memory and tcp/ip.

The error deals with a protocol mismatch.  When locally connecting shared memory/named pipe work. Remote connections are often using tcp/ip or named pipes.
0
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
@arnold

Perhaps.  I have never seen this message before.

But this does not surprise me either. This connectivity protocol resolution is always SQL Server culprit.

@HuaMinChen
You need to do some testing to confirm some information:

1>Please make sure the client does use TCIPIP only to connect.  Connect remotely using the IP followed by the IP address as follows (replace the example IP by the IP of the server)       235.36.36.376, 1433
2> Please answer the previous question.  Do you use an Activedirectory, Local admin or SQL Login to connect to the server  to authenticate ?

Waiting for answer to above questions to go further.  Hope this helps.
0
HuaMin ChenProblem resolverAuthor Commented:
Thanks. Can you please advise, what to further adjust within the machine now? I did encounter the problem, while I tried to logon that using Management studio, within the same machine.
0
arnoldCommented:
Under start, programs, you should have ms sql that includes a configuration tool. Under the server look at the enabled protocols to make sure all three are enabled.  The change requires the restart of the sql server instance.  Within the same interface, all three should be enabled under the client options.

Within ssms, double check the options prior to attempting the connection to see which option it is trying to use. Named pipes, shared memory, tcp/ip and which port

Etc.
0
HuaMin ChenProblem resolverAuthor Commented:
I can see all the relevant protocols have been enabled below. why did I encounter the problem?
t898.png
0
arnoldCommented:
Note the 1433 port is on ip2 supposedly on all interfaces, run netstat -an | find /I ":1433" and look at whether you have a LIstening line.


Note while active is set, enabled is set to no on IP 2.
0
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
@HuaMinChen

Have you tried doing what I suggested in my last post ?  Please connect only remotely using TCPIP after disabling the other protocols.  Can you do that?  From SSMS, enter the IP in management studio followed by the port number.  I gave you an example and I am waiting for the results.  Unless you do that I can hardly help you further.

@arnold
<<Within ssms, double check the options prior to attempting the connection to see which option it is trying to use. Named pipes, shared memory, tcp/ip and which port>>
The client is one thing but the server resolution of the client request is another thing.  I found the hard way the only reliable way to make sure Non-TPC IP protocols are not involved is to have them disabled on the server (with SQL Browser Service turned off) than troubleshoot from there.
0
HuaMin ChenProblem resolverAuthor Commented:
Racimo,
I disabled these 2 protocols

Shared memory
Named pipes

and then tried to connect to it, from one other machine, using the IP and did get these

Cannot connect to 10.202.6.31,1433.

------------------------------
ADDITIONAL INFORMATION:

A network-related 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 connection could be made because the target machine actively refused it.) (Microsoft SQL Server, Error: 10061)

Open in new window

0
arnoldCommented:
I have all three enabled, make sure the firewall rules on the server allow the inbound traffic. Enabling/disabling protocols requires the restart if the service.

Another issue I've seen deal with an incorrect version of the CLI.

.......
Your error points to sql 2005, while the image reflects sql client version as 11 suggesting it is sql server 2012. In this case the sql client library is the issue for the mismatch.
0
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
OK.  So know you can be positive that TCPIP access is invisible from your client box and the previous error message was related to non TCP IP resolution.  Looking at your screenshot please make sure the IPAll TCP IP is enabled.  All your IP seem to be disabled.

Under IPAll please change the port from 1433 to some other value to confirm and select enabled under IPAll.  Try reconnect usin other port than 1433 (Ex:5022)

<<No connection could be made because the target machine actively refused it>>
Please temporarily disable your firewall if any and retry.  Thanks
0
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
As arnold mentionned, please restart your service with each modification
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
It can also be a SPN problem.
Can you run the following commands in DOS prompt and post here the results?
setspn -L PC326VM01\SSEXPR12

Open in new window

setspn -L MSSQLAccount

Open in new window

Substitute MSSQLAccount by the account that is being used to run the SQL Server service.
0
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<It can also be a SPN problem>>
True.  

But first, let's eliminating the server configuration and firewall issues.
0
HuaMin ChenProblem resolverAuthor Commented:
I have also created the Inbound rule on TCP port 1433, but I still get the same problem. Here are what I get

C:\Program Files\Microsoft SQL Server\MSSQL11.SSEXPR12\MSSQL\DATA>setspn -L PC326VM01\SSEXPR12
FindDomainForAccount: Call to DsGetDcNameWithAccountW failed with return value 0
x0000054B
Could not find account SSEXPR12

C:\Program Files\Microsoft SQL Server\MSSQL11.SSEXPR12\MSSQL\DATA>setspn -L sa
FindDomainForAccount: Call to DsGetDcNameWithAccountW failed with return value 0
x0000054B
Could not find account sa

Open in new window

0
Vitor MontalvãoMSSQL Senior EngineerCommented:
The SQL Server service account isn't sa for sure. Check in services which account it's being used for the service.
For the first SPN try to run with the server name only: setspn -L PC326VM01
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
So, what was the issue and how you solved it?
0
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
Please use a the service domain account for SPN registration.
0
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<< how you solved it?>>
I am interested as well :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Legacy OS

From novice to tech pro — start learning today.