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

JohnPapa · Feb 1, 2024

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:

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 · Feb 1, 2024

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 · Feb 2, 2024

you can also try:

Code:

ODBC;DRIVER=SQL Server;SERVER=\\DESKTOP-KQTNJ42\SQLEXPRESS;DATABASE=VF3;UID=savf;PWD=1234

JohnPapa · Feb 2, 2024

cheekybuddha said:

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

JohnPapa · Feb 2, 2024

arnelgp said:
you can also try:

Code:

ODBC;DRIVER=SQL Server;SERVER=\\DESKTOP-KQTNJ42\SQLEXPRESS;DATABASE=VF3;UID=savf;PWD=1234

Will try thanks

AHeyne · Feb 2, 2024

Maybe a question of an active firewall on the server (which seems to be a desktop pc)?

JohnPapa · Feb 2, 2024

AHeyne said:
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.

arnelgp · Feb 2, 2024

Code:

"By default Remote connections in SQL Server Express is turned off."

that is the answer from the below link, you might need to do some steps on the server computer:
c# - How to run desktop application using SQL Server database on other computer connected with LAN - Stack Overflow

JohnPapa · Feb 2, 2024

arnelgp said:
Code:

"By default Remote connections in SQL Server Express is turned off."

that is the answer from the below link, you might need to do some steps on the server computer:
c# - How to run desktop application using SQL Server database on other computer connected with LAN - Stack Overflow

Many thanks, I will check this this afternoon and see if it works. It is promising.

JohnPapa · Feb 2, 2024

JohnPapa said:
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 · Feb 16, 2024

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 · Feb 16, 2024

Can your windows 10 computer see DESKTOP-KQTNJ42 in the Network section of Windows Explorer?

Have you checked your firewall rules?

JohnPapa · Feb 17, 2024

cheekybuddha said:
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 · Feb 17, 2024

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

The error which i receive is

JohnPapa · Feb 17, 2024

I also found a suggestion of enabling Named Pipes.

cheekybuddha · Feb 17, 2024

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 · Feb 17, 2024

cheekybuddha said:
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 · Feb 19, 2024

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

ComputerVersteher · Feb 19, 2024

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 · Feb 19, 2024

JohnPapa said:
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.

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

Registered User.

AWF VIP

..forever waiting... waiting for jellybean!

Registered User.

Registered User.

Registered User.

Registered User.

..forever waiting... waiting for jellybean!

Registered User.

Registered User.

Registered User.

AWF VIP

Registered User.

Registered User.

Registered User.

AWF VIP

Registered User.

Registered User.

Member

Active member

Similar threads

Users who are viewing this thread