Cannot connect to SQL Server on a network (1 Viewer)

JohnPapa

Registered User.
Local time
Today, 07:33
Joined
Aug 15, 2010
Messages
954
I am trying unsuccessfully to connect my O365 Access FE to the SQL Server on a network.

On a standalone pc there is no problem. The SQL Server properties and Connect string are shown below:

1706816320510.png



ODBC;DRIVER=ODBC Driver 17 for SQL Server;SERVER=DESKTOP-KQTNJ42\SQLEXPRESS;UID=savf;Trusted_Connection=No;APP=Microsoft Office;DATABASE=VF3;PWD=1234

I am trying this for the first time. Do I need to set up something in SQL Server?

The pc where the SQL Server is located is called "Server" and I tried unsuccessfully to use

\\Server\SQLEXPRESS

I was not happy with the administrator naming the server "Server"...

Any ideas?
 

cheekybuddha

AWF VIP
Local time
Today, 05:33
Joined
Jul 21, 2014
Messages
2,337
Try specifying the port number:
Code:
ODBC;DRIVER=ODBC Driver 17 for SQL Server;SERVER=\\Server,1433;UID=savf;APP=Microsoft Office;DATABASE=VF3;PWD=1234
or:
Code:
ODBC;DRIVER=ODBC Driver 17 for SQL Server;SERVER=Server,1433;UID=savf;APP=Microsoft Office;DATABASE=VF3;PWD=1234
or:
Code:
ODBC;DRIVER=ODBC Driver 17 for SQL Server;SERVER=\\Server\SQLEXPRESS,1433;UID=savf;APP=Microsoft Office;DATABASE=VF3;PWD=1234
or:
Code:
ODBC;DRIVER=ODBC Driver 17 for SQL Server;SERVER=Server\SQLEXPRESS,1433;UID=savf;APP=Microsoft Office;DATABASE=VF3;PWD=1234
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 12:33
Joined
May 7, 2009
Messages
19,248
you can also try:
Code:
ODBC;DRIVER=SQL Server;SERVER=\\DESKTOP-KQTNJ42\SQLEXPRESS;DATABASE=VF3;UID=savf;PWD=1234
 

JohnPapa

Registered User.
Local time
Today, 07:33
Joined
Aug 15, 2010
Messages
954
Try specifying the port number:
Code:
ODBC;DRIVER=ODBC Driver 17 for SQL Server;SERVER=\\Server,1433;UID=savf;APP=Microsoft Office;DATABASE=VF3;PWD=1234
or:
Code:
ODBC;DRIVER=ODBC Driver 17 for SQL Server;SERVER=Server,1433;UID=savf;APP=Microsoft Office;DATABASE=VF3;PWD=1234
or:
Code:
ODBC;DRIVER=ODBC Driver 17 for SQL Server;SERVER=\\Server\SQLEXPRESS,1433;UID=savf;APP=Microsoft Office;DATABASE=VF3;PWD=1234
or:
Code:
ODBC;DRIVER=ODBC Driver 17 for SQL Server;SERVER=Server\SQLEXPRESS,1433;UID=savf;APP=Microsoft Office;DATABASE=VF3;PWD=1234
Will try thanks
 

AHeyne

Registered User.
Local time
Today, 06:33
Joined
Jan 27, 2006
Messages
94
Maybe a question of an active firewall on the server (which seems to be a desktop pc)?
 

JohnPapa

Registered User.
Local time
Today, 07:33
Joined
Aug 15, 2010
Messages
954
Maybe a question of an active firewall on the server (which seems to be a desktop pc)?
Yes, the "Server" is a desktop pc. I believe this is the one thing we investigated.
 

JohnPapa

Registered User.
Local time
Today, 07:33
Joined
Aug 15, 2010
Messages
954

JohnPapa

Registered User.
Local time
Today, 07:33
Joined
Aug 15, 2010
Messages
954
Many thanks, I will check this this afternoon and see if it works. It is promising.
Success, but a few questions.

I went through the list of the post you referred to above:

  • Code:
    Open SQL Server Configuration Manager
    Select SQL Server Network Configuration
    Chose your instance of SQL Server
    Make sure that TCP/IP protocol is enabled
    Right click TCP/IP protocol
    Select properties
    Click IP addresses tab
    Scroll down to IP4. The IP address of the server should be here. Set active to yes and enabled to yes. Set TCP port to 1433 (don't know if this is necessary. Can some expert comment)
    Scroll down to IPAll. Set TCP port to 1433
    Make an inbound firewall rule for port 1433
    open sql server management studio, right click server instance, properties->connections-> allow remote connections. Security-> SQL Server and Windows Authentication mode
    restart sql server service
    restart sql server browser
It worked when I used SERVER = SERVER (in my case the server name is Server), BUT only when I downloaded SSMS.

Maybe downloading SSMS sets some kind of connection to the server. Maybe someone can shed some light.

In any case many thanks for the help.
 

JohnPapa

Registered User.
Local time
Today, 07:33
Joined
Aug 15, 2010
Messages
954
At present we have a network with 4 pcs. The 3 run Windows 11 and they run ok. The 4th pc runs Windows 10 and cannot connect to the SQL Express

All 4 pcs use:

Code:
ODBC;PROVIDER=MSDASQL;DRIVER=ODBC Driver 17 for SQL Server;SERVER=DESKTOP-KQTNJ42\SQLEXPRESS;UID=savf;Trusted_Connection=No;APP=Microsoft Office;DATABASE=VF3;PWD=1234

The problem is in the .Open command in the following Function (only in the Windows 10 pc)

Code:
Function IsDBServerAvailable(strCn As String) As Boolean
On Error GoTo Err_IsDBServerAvailable


  Const adUseClient As Integer = 3, _
        adStateOpen As Integer = 1, _
        ODBC As String = "ODBC;", _
        PROVIDER As String = "PROVIDER=MSDASQL;"
       
  If Is32BitAccess Then
    If InStr(strCn, PROVIDER) = 0 Then
      strCn = Replace(strCn, ODBC, ODBC & PROVIDER)
    End If
  End If


  With CreateObject("ADODB.Connection")
    .ConnectionString = strCn
    .CursorLocation = adUseClient
    .Open
    If .State = adStateOpen Then
      IsDBServerAvailable = True
      .Close
    End If
  End With


Exit_IsDBServerAvailable:
  Exit Function


Err_IsDBServerAvailable:
  Select Case Err.Number
  Case Else

    'CCC  You cannot access the database. Please contact your system administrator.
    MsgM (10068)

    DoCmd.OpenForm "frmSerialLocalPre", , , , , acDialog
    DoCmd.OpenForm "frmSerialLocal", , , , , acDialog, "QUIT"
   
    Application.Quit


  End Select
  Resume Exit_IsDBServerAvailable


End Function
 

cheekybuddha

AWF VIP
Local time
Today, 05:33
Joined
Jul 21, 2014
Messages
2,337
Can your windows 10 computer see DESKTOP-KQTNJ42 in the Network section of Windows Explorer?

Have you checked your firewall rules?
 

JohnPapa

Registered User.
Local time
Today, 07:33
Joined
Aug 15, 2010
Messages
954
Can your windows 10 computer see DESKTOP-KQTNJ42 in the Network section of Windows Explorer?

Have you checked your firewall rules?
I have a meeting onsite shortly (on a Saturday). The W10 pc can see the Server (which is just a pc). I was able to create a mapped drive to the Server and SSMS can see the DB (VF3) which resides in the SQL Server on the Server. We have checked the firewall rules and will check them again.
 

JohnPapa

Registered User.
Local time
Today, 07:33
Joined
Aug 15, 2010
Messages
954
Just a clarification "DESKTOP-KQTNJ42" is the name of my pc, which is not on the client's Network

The Network has 4 pcs called
1) Server
2) pc1
3) pc2
4) pc3

The problem is with pc2 with runs Windows 10. The other 3 pcs run Windows 11.

The Network section of pc2 is indicated below and does not show pc Server
1708172796766.png

The error which i receive is

1708172281555.png
 

JohnPapa

Registered User.
Local time
Today, 07:33
Joined
Aug 15, 2010
Messages
954
I also found a suggestion of enabling Named Pipes.
 

cheekybuddha

AWF VIP
Local time
Today, 05:33
Joined
Jul 21, 2014
Messages
2,337
Look at the network settings of pc2 and make sure it it's able to see the rest of the lan. IIRC, Windows gives network settings silly names like 'home' and 'office' or similar. Select whichever will allow it to see the rest of the network
 

JohnPapa

Registered User.
Local time
Today, 07:33
Joined
Aug 15, 2010
Messages
954
Look at the network settings of pc2 and make sure it it's able to see the rest of the lan. IIRC, Windows gives network settings silly names like 'home' and 'office' or similar. Select whichever will allow it to see the rest of the network
Will do thanks.
 

JohnPapa

Registered User.
Local time
Today, 07:33
Joined
Aug 15, 2010
Messages
954
I have created a 2 pc network with a desktop and a laptop.

The desktop runs W10 32-bit A365 and is called DESKTOP-KQTNJ42
The laptop runs W1112341234sa1234savf 64-bit A365 and is called JohnP

The SQl Server is on the desktop and I can log onto SSMS on both with Server = DESKTOP-KQTNJ42 Username = savf Password = 1234

I use the following connection string from the desktop success fully
ODBC;PROVIDER=MSDASQL;DRIVER=ODBC Driver 17 for SQL Server;SERVER=DESKTOP-KQTNJ42\SQLEXPRESS;UID=savf;Trusted_Connection=No;APP=Microsoft Office;DATABASE=VF3;PWD=1234

I use the same connection string without PROVIDER=MSDASQL and receive the following error

Code:
Error No.: -2147467259


Description: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

I have checked the firewall rules on both sides. I can ping the pcs from each other. pc
 
Local time
Today, 06:33
Joined
Feb 27, 2023
Messages
43
Check bitness of ODBC Driver (should match Office bitness). If Office (not Windows!) is x64 install x64 version of ODBC-Driver, if Office is x86 install x86 version of ODBC-Driver!
 

tvanstiphout

Active member
Local time
Yesterday, 21:33
Joined
Jan 22, 2016
Messages
269
1. I have checked the firewall rules on both sides.
2. I can ping the pcs from each other. pc
re 1. That means very little. Put some meat on it by including a screenshot of the rule(s) that you think open the firewall.
re 2. That means very little. Ping does not use TCP/IP while SQL Server does (in one of its configurations).

I would leave ODBC out of the equation for a moment, and run all tests using SSMS.

SERVER=DESKTOP-KQTNJ42\SQLEXPRESS: this means you're connecting to an instance of SQL Server, not the default instance. This will help you narrow down online articles. It requires another app to be running on the server (SQL Server Browser) and an extra port open 1434 UDP by default.
 

Users who are viewing this thread

Top Bottom