How to find out why am I getting connection failure to my server?

I guess

netstat -abn | findstr "XXXX"

or

netstat -ano | findstr "XXXX"

it would be helpful to know what the old ADP sql connection version was.
Usually if you use sql native client a version, can connect to itself, one prior but it might not work well with a newer.
The other possibility is the credentials used in the old are not the same as the ones in the new.
so when you create a new ADP, you enter the valid login for server_new, which are not the same as the login on server_old....

Hi Experts,

Sorry for the delay, its just we are middle setting up a new server and everything is going the opposite direction..
Yesterday we spend a whole day setting up a test environment and was counting on users to test it today, just to find out (for some unknown reason..) that only my pc was able to connect to the server..

@Huseyin,
I'm not changing any ports while switching files, however when I tried your nothing showed up in cmd screen.
The port is the default port.

Now back to this issue, my old connection is to a SQL 2005 server if that matters.

@Arnold,
1- In a ADP there is no place I choose which driver to connect to..
2- Re credentials, of course I'm comparing in the same manner, meaning if old login/pwd is sa/sa and new is sa/123 then this is whats working while switching back and forth with a freshly new created ADP, however when trying to change the old ADP file I get the error OP.

Thanks,
Ben

how do you set the connection string?
what driver are you using?
are you using ODBC Manager?

See attached what we have to fill in.

Thanks,
Ben
Untitled.png

ok, then you need a valid login/password here...
what do you have in advanced tab,
and what is happening when you test?

What about advanced tab, when you test the connection what happens.
What SQL version is running on the old server vs new server? Servername\instance, and the new server servername\instance?
You are likely missing something small when modifying existing ADP connection versus when you are setting up a new one.

Attached properties of advanced tab, however I never had to setup something there, same goes for the all tab.

My old sql is SQL\Sqlexpress and is SQL 2005
My new server is sqlserver1\exp08 and its SQL server 2008 r2.

Nothing do I do different when I setup new than of trying to change the old one.
Is there a way to see whats access doing different behind the scenes? (this is kind of what the guy was trying to determine thru netstat..)

Thanks,
Ben
Untitled.png

install new sql drivers then...

SQL Server Drivers
https://docs.microsoft.com/en-us/sql/connect/sql-server-drivers

Installed and still same problem.
However I just noticed that this is only happening in my pc, when I tried same in a different pc it worked fine.
So what must be repaired here?

Thanks,
Ben

did you disable FW and UAC?

what driver did you select?

what is the content of udl file?

when you open odbc manager, do you see sql server native drivers?

you may install this as well

https://www.microsoft.com/en-us/download/details.aspx?id=29065

it should add native sql drivers...

Can you post the link to the latest SQL Server native client download.
I see my pc dont have that, most likely this is the issue.

Thanks,
Ben

Actually I just installed native client (see attached) and so far problem still occurs.
guess we must look into different directions, perhaps office repair..

Thanks,
Ben
Untitled.png

huseyin posted the SQL NCLI for sql server 2012.
https://www.microsoft.com/en-us/download/details.aspx?id=44272 for sql server 2008 R2....

which one do you want/need?

"sql server <version> Native Client Library" before you can find them by components, it seems now they are bundled as part of sql server <version> feature pack.

Which mode are you using to connect on the client that does not have this issue, an ODBC driver or an SQL native client /Sql server.

are you using a SYSTEM DSN? or as your image points, you are using an Access connection....

did you stop FW?
any av malware software running?

@Arnold,
This is an ADP project which has its own way to connect, I dont get to choose a driver or use any DSN files.

@Huseyin,
FW is being managed by AVG, see attached.

Thanks,
Ben
Untitled.png

Its getting late for me, will resume tom..have a good night!

Thanks,
Ben

Which version access do you have versus the other workstation? The data link connection offers different options.  Is your version older than the one the other person has?
The old versus new likely has the data link that is not compatible with the SQL server 2008 version on your system.
The creation of a new confirms that it is not an issue with your system or the server but something related to the settings of the existing connection.

If not mistaken, adp can be configured to use a system/user DSN versus specifying a server..........

Hi,

Both pc's have MS Access 2003 SP3 installed.
Both OS are Win7 Pro SP1.

The only difference I can see is that the other have SQL Native client 10 and 11, while in mine I only have 11, let me know if that matters.
Or perhaps after after installing native client I do need to restart my pc (although it was not requested)?

What else can come into the mind?

PS. Can you post a link describing how to select DSN or specific drivers on ADP?

Thanks,
Ben

it is hard to say from here what the issue is in terms of being to diagnose and provide guidance on how to resolve.

See if the discussion dealing with using ODBC, though your scenario seems to be a DB driven backend with an ACcess front /gui.
https://www.techonthenet.com/access/tutorials/passthrough/basics02.php

Hi,

I cant see how this link is helpful in ADP's, as in ADP there is no such a concept of importing/linking to objects, and besides the entire idea of pass-through query is not relevant in ADP.

I did realized that the problem is only to this new server we are in middle of setting up, as we have an old server 2008 in place and when I switch connection to that server it works fine.

So perhaps something is not setup right in this new server..?

Thanks,
Ben

When sone thing is not right on a server, the issue is not transactional, it righter works or it does not. Do you have firewalls and the new server and systems in question are on separate segments.
The other when you add a new data link, the connection to the new server works.

Presumably on the new server, SQL config TCP/ip server connections are enabled.

Check the port bindings noth old/מew servers have 1433 as the port to which SQL binds?

Try when you connect to the old, use netstat -an to see the port on the old server
Then using a new connection, see what the remote SQL port is used on the new server.

SQL bat ice client 11 is from SQL server 2012', 10.0 is from SQL server 2008, 10.5 is from SQL server 2008 R2 if not mistaken.
Install the second SQL ncli 10.0 or 10.5 and see of both are installed it works.

Hi Arnold,

Both servers have 1433 port setup which is the default.
TCP/IP is enabled in new server.

I tried multiple times to install SQL Native client 10 and got errors, see attached.
My PC is Win7 Pro 64 Bit, and I choose the 64 version, not sure why it does not accept.

Perhaps I have to uninstall SQL native client 11 first?

What is the netstat command I should use in order to see when it doesn't work where is it trying to connect?

Thanks,
Ben
Untitled.png

it tells you that the version of the product you are trying to install is not supported on your platform
1) the sql server 2008 native client you downloaded is not suitable for your system.
a) either you are tring to install a 64 bit version on a 32bit.
b) you are installing a version that is not suitable for your system.

double check your system is 64bit. The feature pack includes both versions.

try https://www.microsoft.com/en-us/download/details.aspx?id=44277
2008
https://www.microsoft.com/en-us/download/details.aspx?id=16978
2008 R2

OK I already uninstalled Native client 11 and then installed 10, also installed/uninstalled other drivers as well and so far nothing works,..

btw, also tried another pc in place with Access 2010 and had the same issue.

FYI- The pc that doesn't have the problem has SSMS 2008 installed, so perhaps thats also playing here as could be as its very likely some additional drivers/internal files are running there as well..

Thanks,
Ben

Actually I realized the problem is much deeper, not only related to ADP.

I just tried re-linking an existing table in a simple access mdb to the new server with a new DSN and I get the attached (while the DSN file when creating it thru ODBC manager works fine).

PS. I do have a feeling something is not setup right in the new server, just not sure what\where..?

Becoming kind of desperate to find a solution here!!

Thanks,
Ben
Untitled.png

Is SQL server set to Windows Authentication Only?
if yes, change it to "SQL and Windows Authentication" under the Instance properties

Ssms includes the SQL ncli ...... During install.
Check the new server sl server configuration dealing with whether the server component has TCP/ip protocol enabled.

Did you migrate the SQL logons from the old server to the new server?
Ms has SQL server login transfer guide that deals with adding two SPs to the old SQL server and then running..
Those deal with exporting the logins that properly use the sids.

Presumably the new server has the old db backed up/restored on the new.... If SQL logons are used as huseyin suggested, the db will have references to those SQL logins by way of the AIDS. So if you manually created the logins they will have unique sids and will not be authorized to access the db on any capacity.

No, its setup as SQL Authentication.

I'm trying to compare whats different from our other SQL 2008 server where everything works, so far I only realized under TCP/IP that one of the addresses in the old server was enabled, so I enabled here as well, then restarted services but no help..

Thanks,
Ben

Login transfer
https://support.microsoft.com/en-us/help/918992/how-to-transfer-logins-and-passwords-between-instances-of-sql-server

You could add the two SPs to each system, run them on each to list the logins and then compare the sids
Check the database (not database server) , security listing the users attached to the DB.

Try using the ssms to see whether using GE old username/passwords you can connect to the new server....

create a new login and set permissions and try to connect with new login

Hi Experts,

I will have to leave the office as its already pass midnight here, sorry for that..hope to finalize it by tom.

If thats kind of complicate task I may ask the manager approval for creating a GIG..

FYI- I did try that with a newly created user as well, same error.

Thanks,
Ben

Please clarify the request? TCP is the means of connecting to a remote SQL server. If the Access front end is running on an RDS/RDP and the SQL server is installed on the same system, then shared memory and named pipes will be enough for Access/ADP to connect to the local SQL instance without the need for TCP...

Hi Arnold,

See attached the difference between the new server setup (top picture) and the old server setup (bottom).

Both servers are on the network and Access file is running locally on the pc I'm connected to, therefore I wonder why the new server needs the tcp hint to get connected.

Thanks,
Ben
Untitled.png

It is hard to answer based on that info, check Windows 2008 advanced firewalls check whether named pipes is allowed through the firewall...
What does your desktop SQL client connection defaults to?...

Not sure where do you want me to check on Windows8 local or on the server, none of those pc's has win8, and where come named pipes to play here, dont we want always to use tcp?

See attached the SQL defaults connection.

Thanks,
Ben
Untitled.png

It is difficult from this vantage point to determine the behavior that you have difference between two versions .......
Explicitly setting TCP. As part of the connection going ......

Thanks very much my experts for all your efforts!
PS. If you happen to know why is the tcp necessary here please share it with us.
Thank you.