Kamis, 02 Juni 2022

Re: [MSAccessProfessionals] Connection to SQL with saved User and Password

Create a backup of your db first.
Delete all Link table and create new link table.
Pay attention on the wizard because at the end it will ask if you want to save the password. You answer Yes.

On Fri, Jun 3, 2022, 3:34 AM Doyce Winberry <doyce.winberry@xpo.com> wrote:

Hey I think I have solved this one. I found a video that showed how to create a file DSN and then edit the file and add a line at the bottom that says PWD = password. I did that and then relinked my tables using the file and so far it is working great. Thanks for your response Duane and Paul.

 

Doyce Winberry

Manufacturing

Manager Systems

 

XPOLogistics

2001 Benton Street

Searcy, AR 72143 USA

O: +1 501-207-5973   M: +1 501-207-2269

 

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> On Behalf Of Duane Hookom
Sent: Thursday, June 2, 2022 12:57 PM
To: MSAccessProfessionals@groups.io
Subject: Re: [MSAccessProfessionals] Connection to SQL with saved User and Password

 

Here is a blog post from Ben Clothier (one of the smartest developers I know) http://accessblog.net/2012/04/make-sql-server-linked-table-more.html?m=1 Sent from my mobile On Jun 2, 2022, at 11:22 AM, Doyce Winberry <doyce.winberry@xpo.com>

ZjQcmQRYFpfptBannerStart

This Message Is From an External Sender

This message came from outside your organization.

ZjQcmQRYFpfptBannerEnd

Here is a blog post from Ben Clothier (one of the smartest developers I know) http://accessblog.net/2012/04/make-sql-server-linked-table-more.html?m=1

 

Sent from my mobile



On Jun 2, 2022, at 11:22 AM, Doyce Winberry <doyce.winberry@xpo.com> wrote:



Basically, if I link to the tables and use the service account, I have read/write access. If I use the normal windows account I get only read access under their new rules. So I need to connect to the SQL Server using the service account login ID and password. But they don't want the users to know the password so I need to put it in the connection string. I'm trying to figure out how to do that.

 

Doyce Winberry

Manufacturing

Manager Systems

 

XPOLogistics

2001 Benton Street

Searcy, AR 72143 USA

O: +1 501-207-5973   M: +1 501-207-2269

 

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> On Behalf Of Duane Hookom
Sent: Thursday, June 2, 2022 11:08 AM
To: MSAccessProfessionals@groups.io
Subject: Re: [MSAccessProfessionals] Connection to SQL with saved User and Password

 

I am not that familiar with Sarbanes Oxley but I would expect the actual account making changes or accessing the data would be critical. I'm not sure if that is baked into a service account. Duane From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>

ZjQcmQRYFpfptBannerStart

This Message Is From an External Sender

This message came from outside your organization.

ZjQcmQRYFpfptBannerEnd

I am not that familiar with Sarbanes Oxley but I would expect the actual account making changes or accessing the data would be critical. I'm not sure if that is baked into a service account.

 

Duane


From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of Doyce Winberry <doyce.winberry@xpo.com>
Sent: Thursday, June 2, 2022 10:43 AM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: Re: [MSAccessProfessionals] Connection to SQL with saved User and Password

 

Duane,

 

I can't answer that question because it doesn't make sense to me either. We have been using an AD group but they say it is not in compliance with Sarbanes Oxley.

 

Doyce Winberry

Manufacturing

Manager Systems

 

XPOLogistics

2001 Benton Street

Searcy, AR 72143 USA

O: +1 501-207-5973   M: +1 501-207-2269

 

From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> On Behalf Of Duane Hookom
Sent: Thursday, June 2, 2022 10:28 AM
To: MSAccessProfessionals@groups.io
Subject: Re: [MSAccessProfessionals] Connection to SQL with saved User and Password

 

Hi Doyce, I'm confused why a "service" account would be used. I have typically seen service accounts used when one system needs to connect to another such as cross SQL Server or some software collecting data that needs automation

ZjQcmQRYFpfptBannerStart

This Message Is From an External Sender

This message came from outside your organization.

ZjQcmQRYFpfptBannerEnd

Hi Doyce,

I'm confused why a "service" account would be used. I have typically seen service accounts used when one system needs to connect to another such as cross SQL Server or some software collecting data that needs automation to connect to SQL Server. Our SQL admins have always encouraged us to create (or request creation) of AD security groups and adding individuals to the groups. The AD group is then granted specific rights within the SQL database.

 

Regards,

Duane

 


From: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io> on behalf of Doyce Winberry <doyce.winberry@xpo.com>
Sent: Thursday, June 2, 2022 7:48 AM
To: MSAccessProfessionals@groups.io <MSAccessProfessionals@groups.io>
Subject: [MSAccessProfessionals] Connection to SQL with saved User and Password

 

Hello everyone. I'm using Access 2016 and have a DB that connects to some SQL server tables. Lately my corporate DB team has said we are not Sarbanes Oxley compliant because individual users have write rights to the SQL tables so they propose that we use a "service account" to connect to the SQL tables instead of individual accounts. When they remove the write rights from my individual accounts the DB breaks because it needs to write to the SQL tables. I'm using an ODBC connection setup as a system DSN to connect to the SQL tables. When I change the DSN to use the service account they want me to use, it does not remember the password. I'm not supposed to share the password with the users. So how can I connect to the SQL tables with the service account and password and have the DB "remember" the password. I've found examples of connection strings with userID and passwords but don't know where to put them in the DB. 
Thanks in advance.
Doyce Winberry

_._,_._,_

Groups.io Links:

You receive all messages sent to this group.

View/Reply Online (#116219) | Reply To Group | Reply To Sender | Mute This Topic | New Topic
Your Subscription | Contact Group Owner | Unsubscribe [sugeng.panjalu.access@blogger.com]

_._,_._,_

Tidak ada komentar:

Posting Komentar