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

[JohnPapa]

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]

Try specifying the port number:

ODBC;DRIVER=ODBC Driver 17 for SQL Server;SERVER=\\Server,1433;UID=savf;APP=Microsoft Office;DATABASE=VF3;PWD=1234

or:

ODBC;DRIVER=ODBC Driver 17 for SQL Server;SERVER=Server,1433;UID=savf;APP=Microsoft Office;DATABASE=VF3;PWD=1234

or:

ODBC;DRIVER=ODBC Driver 17 for SQL Server;SERVER=\\Server\SQLEXPRESS,1433;UID=savf;APP=Microsoft Office;DATABASE=VF3;PWD=1234

or:

ODBC;DRIVER=ODBC Driver 17 for SQL Server;SERVER=Server\SQLEXPRESS,1433;UID=savf;APP=Microsoft Office;DATABASE=VF3;PWD=1234

 

[arnelgp]

you can also try:

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

 

[JohnPapa]

Try specifying the port number:

ODBC;DRIVER=ODBC Driver 17 for SQL Server;SERVER=\\Server,1433;UID=savf;APP=Microsoft Office;DATABASE=VF3;PWD=1234

or:

ODBC;DRIVER=ODBC Driver 17 for SQL Server;SERVER=Server,1433;UID=savf;APP=Microsoft Office;DATABASE=VF3;PWD=1234

or:

ODBC;DRIVER=ODBC Driver 17 for SQL Server;SERVER=\\Server\SQLEXPRESS,1433;UID=savf;APP=Microsoft Office;DATABASE=VF3;PWD=1234

or:

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]

you can also try:

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

Will try thanks

 

[AHeyne]

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

 

[JohnPapa]

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]

"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]

"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]

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:

• 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]

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:

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)

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]

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

Have you checked your firewall rules?

 

[JohnPapa]

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]

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]

I also found a suggestion of enabling Named Pipes.

 

[cheekybuddha]

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]

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]

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

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]

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]

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.