Cannot connect Access to SQL Server Linked Tables - error message loggingin
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.
To connect to a named instance on SQL Server Express with Servername\SQLEXPRESS, you need:
- SQL Server Browser service running,
and its UDP port 1434 open in the firewall.
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.
- The fixed TCP port for your instance open in the firewall.
You set this in SQL Server Configuration Manager
- query multiple IIF statement
- SQLExecute() returns SQL_NEED_DATA
- Optimizing OLE DB Destination for Fast load from Oracle to SQL Server for SSIS
- Is there any better option to apply pagination without applying OFFSET in SQL Server?
- Multiple Security Predicates (FILTER|BLOCK) on same Table
- Untrusted Certificate Error when connecting to SQL Server running in Docker from .NET 7 API in VS2022
- Can using isnull in a where statement cause problems with using indexes?
- Parsing JSON nodes in the same string
- why is this subquery not working? SQL Server begginer practice case
- How to create a before delete trigger in SQL Server?
- sqlsrv array doesn't return all rows
- How can I use LEFT & RIGHT Functions in SQL to get last 3 characters?
- SQL Stored Procedures not finishing when called from Python
- How to DROP multiple columns with a single ALTER TABLE statement in SQL Server?
- Rank rows by specified partition
- SQL Get "ISO Year" for ISO Week
- Trouble with sort order using Lead with window function
- Relational table design with same type in a many-to-many relationship
- Select 1 row from the dataset which satisfies a particular condition
- How to remove or alter the dbo prefix from SQL Server 2012 tables?
- Return Value of Previous Row Column
- Case expression for Order By clause with Desc/Asc sort
- Get lag value from the last row where value from other column = 'n'
- Date format in dd/MM/yyyy hh:mm:ss
- Get list of databases from SQL Server
- Pyodbc: Login Timeout Error
- Error when installing Microsoft Sql Server 2019, cannot find the microsoft OLE DB Driver
- How to create and use database directly after creation in SQL Server?
- Database Naming Conventions by Microsoft?
- How to compare values which may both be null in T-SQL