ODBC connection and users

[barbola]

I was reading this thread, and like this poster we have several users with access to the sa password.

http://www.tek-tips.com/viewthread.cfm?qid=1296670&page=1

The reason is because we have alot of outside applications (Excel, Access, VB) that use GP data and often the user is prompted for the sa password.

I think there is a way so that the user can use their GP login. Does anyone know what needs to be done to allow that? I am slowly converting alot of the Access links to using ADO so that data can't be modified outside of GP but this would help in the meantime.

thanks.

 

[klewis10367]

What Version are you on? If you are on Version 8.0 or Higher you can not use your Great Plains User ID's, you will either have to set up new logins on the SQL server or use Windows Authentication. Either way you have to physically give access to each table that you want the users to have. If you change the Great Plains Users password in Enterpise manager it will force them to change it again when they log into Great Plains. The reason why the sa pops up is probably because whoever set up the odbc connection used it to set up the connection. Also, depending what type of user authentication you use (sql or Windows) you must make sure your ODBC connection is set up that way.

 

[barbola]

We are on SQL Server 2000 and GP 9.0. Each user has a DSN so I guess we could change them to use windows authentication. I'll have to talk to our network guys.

thanks
barb

 

[Alcar]

If the window authentication is not an option for your network 'guys' then I would suggest this:

create application logins in your GP database.. let me give you an example:

we have a report program that we created using VB. Some of the reports using this program query GP data, so when we set up the report to be ran through our program we need to authenticate it against the GP DB.
We created db accounts that have permission to the view that reports need...

Program: Company Report Viewer 2.0
User: CompRepViewerNormal
User: CompRepViewerSpecial

so we have these two users in our db that can access different views in our GP environment. (names have been changed to protect the innocent )

so not only the final user don't have to remember the passwords, but we have a way to select who has more access and we can easily administer the users and protect our GP data.

sa is something I rarely use... the pass is complicated, secured, and changed often (don't get me started...)
The moment I find out that a 'power-user' has gotten a hold of the sa password, I change the sa password, run to the user and have a nice talk with them until they spill out WHO and HOW they got a hold of it...



Daren J. Lahey
Programmer Analyst
FAQ183-874 contains Suggestions for Getting Quick and Appropriate Answers to your questions.

 

[barbola]

Thanks for the ideas. We're not quite there yet as far as hiding the sa password from some users. I have to start making it a priority though. This will help

barb

 

[Alcar]

I would make it an immediate priority.
If you need higher consensus... then just go to the big boss with one of the power users.. sit on his computer and have the power user create an adp project via access and authenticating via sa... then pull up the bosses SSN...

we did that once.. he gave us full powers to close any security holes in our system... and he didn't care how long the system would be down... he just didn't want that to happen anymore... can't blame him

I once had to shut down systems without warning... when I showed the data that spilling out.. they agreed I did a good thing...
remember... nobody wants their private information posted on some website

a real quick fix is to create a db user for each power user and then give the dbread access... that should allow them to read everything but change nothing

then work on the security breaches... plus you will be able to monitor what user is accessing what data =)

tata!


Daren J. Lahey
Programmer Analyst
FAQ183-874 contains Suggestions for Getting Quick and Appropriate Answers to your questions.

 

[barbola]

haha ya well I'm the one who gave out the password so it would be my azz.

It is a high priority though. I will start next week!

b.

 

[Alcar]

heheh *slaps on your hand*

well if you provide them with a good/quick resolution they might forgive ya

Daren J. Lahey
Programmer Analyst
FAQ183-874 contains Suggestions for Getting Quick and Appropriate Answers to your questions.