0

I am trying to set up a test bed in my office with two PCs both running Win 11 Home. PC1 has the latest SQL Server Express 2022. PC2 has SSMS installed.

Ideally I will run MS Access as clients into SQL SE.

Q1 Should this configuration work?

For all my reading I have not found any literature to say I can't do this. If we can't do this, why can't I connect PC2 with SS Management Studio to PC1 with SQL Server Express?

This is what I have done:

  1. On PC1 (Host) I have tested connection locally
  2. On PC1, in SSMS I have "Allowed remote connections to this server" and Set to 'Windows Authentication'
  3. On PC1  I have set enabled  TCP, via Configuration Manager on PC1 I have set set IPAll to 49172, via Configuration Manager  (Because it is SQLEXPRESS)
  4. PC1 Windows firewall set an inbound rule to allow TCP 49172PC1 Windows firewall set an inbound rule to allow UDP 1434 (SS Browser)
  5. PC1 set SS Browser to automaticPC1 stopped and restarted SQL Server (services)
  6. PC2 via Powershell I have 'Test-NetConnection 192.x.x.x. -port 49172'  and all works fine.
  7. PC2, SSMS I have tried to connect to a Database Engine with  'tcp:PC1\SQLEXPRESS' and 'tcp:PC1\SQLEXPRESS, 49172', 
  8. Disabelled Win Fire wall on both PCs 

But I still cannot connect.

Q 2. What am I doing wrong?

I have poured over may sites and posts such as:

Cannot Connect to Server - A network-related or instance-specific error

SQL network-instance related issue (with error 258)

https://www.linglom.com/it-support/enable-remote-connection-on-sql-server-2008-express/

And read almost the whole Microsoft website. https://learn.microsoft.com/en-us/sql/relational-databases/lesson-1-connecting-to-the-database-engine?view=sql-server-ver16

My Errors:

Server Name = tcp:ABRL08\SQLEXPRESS or tcp:ABRL08\SQLEXPRESS,49172 AND 'Network Protocol' = Default

Cannot connect to tcp:ABRL08\SQLEXPRESS.

ADDITIONAL INFORMATION:

The target principal name is incorrect. Cannot generate SSPI context. (Microsoft SQL Server, Error: 0)

Server Name = tcp:ABRL08\SQLEXPRESS,49172 AND 'Network Protocol' = TCP/IP

Cannot connect to tcp:ABRL08\SQLEXPRESS,49172.

ADDITIONAL INFORMATION:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - TCP Provider: ) (Microsoft SQL Server, Error: -1)

Server Name = tcp:ABRL08\SQLEXPRESS AND 'Network Protocol' = TCP/IP

Cannot connect to tcp:ABRL08\SQLEXPRESS.

ADDITIONAL INFORMATION:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

Thom A
  • 88,727
  • 11
  • 45
  • 75
Dave by 2
  • 1
  • 2
  • Did you restart the SQL Browser service after reconfiguring SQL Server Express? (The Browser service only loads instance configuration as it starts). I would elliminate difficulties with the SQL Browser service and UDP access and just use `Server=tcp:ABRL08,49172;...`. – AlwaysLearning Aug 18 '23 at 06:04
  • Have you considered SQL Logon authentication instead? Windows authentication may require extra ports to be open, especially since the two machines (being Home edition) probably aren't domain-joined. – AlwaysLearning Aug 18 '23 at 06:08
  • @AlwaysLearning I have stopped , started, rebooted, screamed, kicked and smashed keyboard and computer a million times. But i have not tried SQL Logon sound like a good place to start, cheers – Dave by 2 Aug 18 '23 at 06:37
  • For Windows Authentication, run the Kerberos Configuration Manager which is available from Microsoft. It checks if what is required is in place and does it. – Galaxiom Aug 18 '23 at 07:00
  • 1
    Do you actually have sql server running or just sql server management studios – siggemannen Aug 18 '23 at 08:34
  • Remove `tcp:` from the connection and if you have SS Browser running remove also the port. The connection must be `ABRL08\SQLEXPRESS`. Missing the important part: WHAT credential do you pass? USERNAME and password are wrong, since you aren't on DOMAIN, you must explicitly write the machine name: ABRL08\USERNAME and password. – Max Aug 18 '23 at 11:21
  • SQL Server is not supported on Windows Home edition. It works, just no support. Use `Server Name = tcp:ABRL08,49172` if you have a fixed port. Do not permanently disable the firewall, make a firewall rule instead. Yes you will need SQL Logon, because Windows Authentication is not going to work unless the usernames *and* passwords of the local machine accoounts are the same (it's primarily designed for AD domain accounts, not local accounts). – Charlieface Aug 18 '23 at 11:56

0 Answers0