Unable to connect Client to SQL Server

[Bruno Campanini]

Workgroup Layout (not Domain).

Server:
Win 7 Ultimate 64/bit
SQL Server 2012 Evaluation
Office 2013 SP1 64/bit
I've migrate an Access BE via SSMA and I can connect an Access FE
(table links) to the SQL Server database


Client:
Win 7 Ultimate 64/bit
Office 2013 SP1 64/bit
With the same Access FE I cannot connect, via ODBC,
the SQL Server database in the Server PC.

I've tried a lot of things, but still I can't!

Any suggestions?

Bruno

[Erland Sommarskog]

What error message do you get?


--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Bruno Campanini]

Erland Sommarskog has brought this to us :

> Bruno Campanini (brun...@libero.it) writes:
>> Workgroup Layout (not Domain).
>>
>> Server:
>> Win 7 Ultimate 64/bit
>> SQL Server 2012 Evaluation
>> Office 2013 SP1 64/bit
>> I've migrate an Access BE via SSMA and I can connect an Access FE
>> (table links) to the SQL Server database
>>
>>
>> Client:
>> Win 7 Ultimate 64/bit
>> Office 2013 SP1 64/bit
>> With the same Access FE I cannot connect, via ODBC,
>> the SQL Server database in the Server PC.
>>
>> I've tried a lot of things, but still I can't!
>>
>> Any suggestions?
>
> What error message do you get?

Connection failed:
SQL State: '08001'
SQL Error: 5
[Microsoft][Driver 11 for SQL Server]Named Pipes Provider:Could non
open a connection to SQL Server [5].
Connection failed:
SQL State: 'HYT00'
QSL Server Error: 0
[Microsoft][ODBC Driver 11 for SQL Server]Login timedout expired

From that I am unable to uderstand/get any useful information.

Bruno

[Erland Sommarskog]

Bruno Campanini (brun...@libero.it) writes:
> Erland Sommarskog has brought this to us :

>> What error message do you get?
>
> Connection failed:
> SQL State: '08001'
> SQL Error: 5
> [Microsoft][Driver 11 for SQL Server]Named Pipes Provider:Could non
> open a connection to SQL Server [5].
> Connection failed:
> SQL State: 'HYT00'
> QSL Server Error: 0
> [Microsoft][ODBC Driver 11 for SQL Server]Login timedout expired
>
> From that I am unable to uderstand/get any useful information.
>

Well, at least then I know that it is a connectivity problem and
not a strict authorisation problem.

Could you try changing the server name to something absolutely
wrong and see if you get the same error message, or a different one?

Are you using Windows authentication or SQL authentication?

Sorry for all the question, but troubleshooting connectivity problems on
a distance with a minimum of information is tedious.



--
Erland Sommarskog, Stockholm, esq...@sommarskog.se

[Bruno Campanini]

Erland Sommarskog laid this down on his screen :

> Bruno Campanini (brun...@libero.it) writes:
>> Erland Sommarskog has brought this to us :
>>> What error message do you get?
>>
>> Connection failed:
>> SQL State: '08001'
>> SQL Error: 5
>> [Microsoft][Driver 11 for SQL Server]Named Pipes Provider:Could non
>> open a connection to SQL Server [5].
>> Connection failed:
>> SQL State: 'HYT00'
>> QSL Server Error: 0
>> [Microsoft][ODBC Driver 11 for SQL Server]Login timedout expired
>>
>> From that I am unable to uderstand/get any useful information.
>>
>
> Well, at least then I know that it is a connectivity problem and
> not a strict authorisation problem.
>
> Could you try changing the server name to something absolutely
> wrong and see if you get the same error message, or a different one?

With a wrong server name I got:

Connection failed:
SQL State: '08001'

SQL Error: -1
[Microsoft][ODBC Driver 11 for SQL Server]SQL Server Network
Interfaces: Error Locating Server/Instance Specified
[xFFFFFFFF]

Connection failed:
SQL State: 'HYT00'

SQL Server Error: 0

[Microsoft][ODBC Driver 11 for SQL Server]Login timedout expired

> Are you using Windows authentication or SQL authentication?

SQL Authentication on the default instance MSSQLSERVER

> Sorry for all the question, but troubleshooting connectivity problems on
> a distance with a minimum of information is tedious.

- TCP/IP enabled
- FILESTREAM enabled
- Allow remote connection to the server
- SQL Server Services are running
- SQL Server Browser is running
- SQL Server Agent stopped (it doesn't start!)
- Firewall disabled on Server and Client

It seems all is ok, but it's three days I try to connect a Client,
without any success!

Bruno

[Erland Sommarskog]

Bruno Campanini (brun...@libero.it) writes:
> - TCP/IP enabled
> - FILESTREAM enabled
> - Allow remote connection to the server
> - SQL Server Services are running
> - SQL Server Browser is running
> - SQL Server Agent stopped (it doesn't start!)
> - Firewall disabled on Server and Client
>
> It seems all is ok, but it's three days I try to connect a Client,
> without any success!
>

The error message talks about named pipes, and the error code is 5, which
usually means "Access is denied". There could be two reason it is trying
TCP/IP: TCP/IP is not enabled on the client, or it using named pipes as a
fallback when TCP/IP fails.

You could try enabling Named Pipes on the sarver, but I don't really
recommend it.

Instead try specifying the server as SERVER,tcp to force TCP/IP to see what
error message we get this time.

Also try to make the connection from the command line with SQLCMD. It should
give the same error, but you never know.

[Bruno Campanini]

Erland Sommarskog was thinking very hard :

> The error message talks about named pipes, and the error code is 5, which
> usually means "Access is denied". There could be two reason it is trying
> TCP/IP: TCP/IP is not enabled on the client, or it using named pipes as a
> fallback when TCP/IP fails.
>
> You could try enabling Named Pipes on the sarver, but I don't really
> recommend it.
>
> Instead try specifying the server as SERVER,tcp to force TCP/IP to see what
> error message we get this time.

With "SERVER,192.168.1.204" I get Error 87 and Error 0
With "SERVER,tcp" the same 87 and 0

>
> Also try to make the connection from the command line with SQLCMD. It should
> give the same error, but you never know.

Command line SQLCMD from where?... not from Dos Prompt.
And what sintax?

Bruno

[Erland Sommarskog]

Bruno Campanini (brun...@libero.it) writes:
> With "SERVER,192.168.1.204" I get Error 87 and Error 0
> With "SERVER,tcp" the same 87 and 0

Mea culpa! I completely confused the syntax. That is the syntax to
force a certain port number. This is what you should try to force TCP/IP:

tcp:SERVER

> Command line SQLCMD from where?... not from Dos Prompt.

Why not? If you installed SQL Server Native Client, I believe that you
get SQLCMD with the bundle. But I might be wrong.

> And what sintax?

SQLCMD -S server -U user -P pwd

[Bruno Campanini]

Erland Sommarskog submitted this idea :

Nothing new, even with command line.

Bruno

[Erland Sommarskog]

Bruno Campanini (brun...@libero.it) writes:
> Nothing new, even with command line.
>

So even if you said tcp:SERVER you got which error?

[Bruno Campanini]

After serious thinking Erland Sommarskog wrote :

> Bruno Campanini (brun...@libero.it) writes:
>> Nothing new, even with command line.
>>
>
> So even if you said tcp:SERVER you got which error?

I get Connection string is not valid [67]
Server not found or not accessible

With sqlcmd -S server -H 192.168.1.202 -U xxx -P yyy
I get
Server not found or not accessible

Firewaal is disabled
SQL Server Browser is enabled and started
Protocol IP is enabled

I can connect SQL Server from within the same PC, but not
from a remote PC:
not with sqlcmd
not from within Access
not with a remote SQL Server Management Studio

I'm just becoming crazy!!!

Bruno

[Bruno Campanini]

Bruno Campanini explained on 12-03-14 :

> After serious thinking Erland Sommarskog wrote :
>> Bruno Campanini (brun...@libero.it) writes:
>>> Nothing new, even with command line.

New things, with:

sqlcmd -S P900 -U sa -P brunocam -l 50

I get
1> and after Enter
2> and so on

I thing I get connected... and then?
How can I pass the parameter -l 50 from inside Access?

Bruno

[Erland Sommarskog]

Bruno Campanini (brun...@libero.it) writes:
> New things, with:
>
> sqlcmd -S P900 -U sa -P brunocam -l 50
>
> I get
> 1> and after Enter
> 2> and so on
>
> I thing I get connected... and then?
> How can I pass the parameter -l 50 from inside Access?

Add "Connect Timeout=50" to the connection string.

It appears that your network configuration is having, well, problems.
I have experienced similar at home as well. I had some awful ping
times between my laptop and my desktop. The easy way out was to disable
IPv6, but I flatly refused, and spent some time fighting with my
router about it. It works now, I think.

The default connect timeout is 15 seconds, and that should be enough
in normal environments.

Great to hear that you were able to resolve the issue!

[Bruno Campanini]

Erland Sommarskog pretended :

> Bruno Campanini (brun...@libero.it) writes:
>> New things, with:
>>
>> sqlcmd -S P900 -U sa -P brunocam -l 50
>>
>> I get
>> 1> and after Enter
>> 2> and so on
>>
>> I thing I get connected... and then?
>> How can I pass the parameter -l 50 from inside Access?
>
> Add "Connect Timeout=50" to the connection string.

I don't have any connection string yet, I was operating Access
manually.
Do you have a connection string I can work around on VBAccess?

>
> It appears that your network configuration is having, well, problems.
> I have experienced similar at home as well. I had some awful ping
> times between my laptop and my desktop. The easy way out was to disable
> IPv6, but I flatly refused, and spent some time fighting with my
> router about it. It works now, I think.
>
> The default connect timeout is 15 seconds, and that should be enough
> in normal environments.


> Great to hear that you were able to resolve the issue!

You have been very useful and patient with me.
I thank you very much!

Bruno

[Erland Sommarskog]

Bruno Campanini (brun...@libero.it) writes:
> I don't have any connection string yet, I was operating Access
> manually.

Well, how you would specify the connection timeout in that case is
an Access question. And I dont't know Access.

> Do you have a connection string I can work around on VBAccess?

"Driver={SQL Server Native Client 11.0};Server=SERVER;UID=User;Database=yourdb;Connect timeout=50"

Although, when I look closer, I realise that Connect timeout is
only listed for OLE DB, not for ODBC. But there must be a connect
timeout somewhere, so try the above.