Problem in adding linked table to SQL Server Express (1 Viewer)

Sun_Force

Active member
Local time
Today, 20:11
Joined
Aug 29, 2020
Messages
396
I've installed SQL Server Express 2019 on a Domain Controller.
Yes I know Microsoft suggest to use a Server or at least another stand alone PC, but for now the office is not in a financial situation to pay for a new server.

In Access 365, I started adding linked tables from External data in ribbon, New Data Source - From Database - From SQL Server.
Then selected Link to the data source by creating a linked table in the next window.

I created a new DSN Connection to the server using Windows Authentication and received a TESTES COMPLETED SUCCESSFULLY.
But when the list of tables in target database opens, It's full of a lot of tables starting with sys.
None of other tables that contain data is shown.

If I create the DSN Connection using sql server authentication, I have the full list of the tables I need.


Note: There's no problem between clients and server. The domain has been up and running for more than 5 years and each client machine can access the server resources according to their rights.
Is there anything in SQL Server's Database option that I have to check?


Thanks for any insight.
 

Minty

AWF VIP
Local time
Today, 11:11
Joined
Jul 26, 2013
Messages
10,354
I don't have a SQL server to hand to test, but I think you would need to add the Users into a group (On the security/users tab) that has the appropriate access to the Tables and other database objects.
 

sonic8

AWF VIP
Local time
Today, 12:11
Joined
Oct 27, 2015
Messages
998
I created a new DSN Connection to the server using Windows Authentication and received a TESTES COMPLETED SUCCESSFULLY.
But when the list of tables in target database opens, It's full of a lot of tables starting with sys.
None of other tables that contain data is shown.

If I create the DSN Connection using sql server authentication, I have the full list of the tables I need.
When creating the DSN pay attention to the database you select. You should explicitly select one instead of leaving it as "(Deafult)" becuase different logins may have different default databases configured.
 

Sun_Force

Active member
Local time
Today, 20:11
Joined
Aug 29, 2020
Messages
396
I don't have a SQL server to hand to test, but I think you would need to add the Users into a group (On the security/users tab) that has the appropriate access to the Tables and other database objects.

Thanks for your help.
Domain Users group is already added to the list of users. But still using windows authentication doesn't show the tables. (only sys tables are available.)
 

Sun_Force

Active member
Local time
Today, 20:11
Joined
Aug 29, 2020
Messages
396
When creating the DSN pay attention to the database you select. You should explicitly select one instead of leaving it as "(Deafult)" becuase different logins may have different default databases configured.

thanks for your input.
I've already selected the target database in the list.
 

Minty

AWF VIP
Local time
Today, 11:11
Joined
Jul 26, 2013
Messages
10,354
Thanks for your help.
Domain Users group is already added to the list of users. But still using windows authentication doesn't show the tables. (only sys tables are available.)
Do they have database level access or table level access or some other permissions?
It can get quite complicated with permissions.
 

Sun_Force

Active member
Local time
Today, 20:11
Joined
Aug 29, 2020
Messages
396
Do they have database level access or table level access or some other permissions?
It can get quite complicated with permissions.

Database level Permissions.

Thanks.
 

Sun_Force

Active member
Local time
Today, 20:11
Joined
Aug 29, 2020
Messages
396
The odd part of the problem is if I add the users one by one, there's no problem. I can create linked tables.
But if I add Domain Users group I can not access the tables.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:11
Joined
May 7, 2009
Messages
19,169
if I add the users one by one
i think this is best.
either they request for an access to the db or they can't access it.

btw, why not the Developer version? it is also free and has more features.
 

sonic8

AWF VIP
Local time
Today, 12:11
Joined
Oct 27, 2015
Messages
998
i think this is best.
either they request for an access to the db or they can't access it.
The administrators will love you for this suggestion. Not.

btw, why not the Developer version? it is also free and has more features.
The developer edition cannot be used for production. - Legally that is. Legitimate businesses care about this kind of thing.
 

sonic8

AWF VIP
Local time
Today, 12:11
Joined
Oct 27, 2015
Messages
998
still he can install to test his project?
Sure, he can. But for what purpose? He must not use any of the additional features you mentioned, or the application will break once it is transferred to production on SQL Server Express.
 

sonic8

AWF VIP
Local time
Today, 12:11
Joined
Oct 27, 2015
Messages
998
The odd part of the problem is if I add the users one by one, there's no problem. I can create linked tables.
But if I add Domain Users group I can not access the tables.
There must be a difference in your process between individual users and the AD Domain Group.

Can you provide more details?

Do you use a Database Role that has the permissions assigned or do you assign permissions to individual Logins? - I would recommend using a role as additional layer of abstraction, even though this introduces another layer where potential errors can be made.

Did you double check that the AD User you are using to connect is actually in the AD Group you granted permissions to? - This is the most common cause of such problems I encountered in practice.
 

sonic8

AWF VIP
Local time
Today, 12:11
Joined
Oct 27, 2015
Messages
998
did i enumerate anything?
Irrelevant. You suggested Dev Edition for "more features", none of which can be used when the target platform is express edition.

I will no longer indulge your trolling.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:11
Joined
May 7, 2009
Messages
19,169
indulge your trolling.
you are the troll! you respond to the OP and not to me (you always do that with my post!), the OP will make the final judgement not Any of Us.
 

Sun_Force

Active member
Local time
Today, 20:11
Joined
Aug 29, 2020
Messages
396
Did you double check that the AD User you are using to connect is actually in the AD Group you granted permissions to? - This is the most common cause of such problems I encountered in practice.

Yes, I already checked it.
Since this is setup for a small business with less than 10 users, for now, I added the domain users individually.
There must be a step I'm missing and ending up with this problem.
When everything is up and running, I will read more on this to find out what's happening here.

Million thanks for all the time everybody put on this.
 

Users who are viewing this thread

Top Bottom