1

I have a client for which I am setting up a new SQL Server Express and (on a different computer) connecting their Access front end to that SQL Server. I created an account on SQL Server, changed authentication to SQL Server. I am able to log on to that account with no issues locally (through SQL Server Management Studio) on the server itself, but when I go back to the client machine and try to create either an ODBC connection or connect directly in Linked Table manager, I get the error below. Looking at the error log in SQL Server I can see no failed logins. In Access and/or ODBC I use Servername\SQLEXPRESS, choose SQL authentication and type in the username/password that I created. But it's still being stubborn.

I'm kind of at my wits end with this one. I checked to make sure that login is enabled, that the created database is mapped to this user, but I'm out of answers. Anyone have any ideas? I'm sure it's something really stupid that I'm overlooking, I've used SQL Server for a long time but I'm not an experienced DB Administrator I'm sure it's something really simple I'm overlooking, but I've done this hundreds of times before. And Windows Authentication won't work because it's on a different computer.

enter image description here

Improve this question
3
  • No Active Directory, to get Windows Authentication to work? Firewall configured (e.g. test ping to Server:Sql-Server port). Sql Server Browser Service is running? Commented Jan 22, 2020 at 19:44
  • Thanks. Windows Authentication is just not an option here. I'm advised to use SQL authentication. Ping does work. I'll check the browser service. Would that be on the client PC? Commented Jan 22, 2020 at 20:09
  • serverfault.com/a/977528 may help Commented Jan 22, 2020 at 20:26

2 Answers 2

Reset to default
1

To connect to a named instance on SQL Server Express with Servername\SQLEXPRESS, you need:

  1. SQL Server Browser service running,
    and its UDP port 1434 open in the firewall.

https://learn.microsoft.com/en-us/sql/sql-server/install/configure-the-windows-firewall-to-allow-sql-server-access

SQL Server Browser service
UDP port 1434
The SQL Server Browser service listens for incoming connections to a named instance and provides the client the TCP port number that corresponds to that named instance.

  1. The fixed TCP port for your instance open in the firewall.
    You set this in SQL Server Configuration Manager

https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-a-server-to-listen-on-a-specific-tcp-port

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

This turned out to be the correct answer, thanks! I turned on SQL Server Browser on the remote machine (and TCP/IP and cleared out the dynamic ports) and voila.
0

This looks more like a network setting rather than server issue.

Check if all necessary permissions, configuration and settings on your machine running the server are OK to accept external connections.

Usually its the server that is rejecting the connection for security reasons.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.