[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Create User to access SQL Server DB/Using NT Authentication

Posted on 2011-05-12
Medium Priority
Last Modified: 2012-08-14

We use MS access, SQL server 2005, NT Authentication.Looking to restrict users from going in via ODBC and want to create a username and password through the MSaccess GUI that will pass the credentials through ODBC and not have the user access the SQL server through Excel/Or other ODBC Applications...Whats the best way to accomplish this??
Question by:Atomicsteaks
LVL 10

Expert Comment

ID: 35750235
The best way I know is to have SA set across all the tables.. OR
Disable windows authentication... OR
create passwords that are so strict, wontbe hackable...does this help?
LVL 42

Accepted Solution

dqmq earned 2000 total points
ID: 35750345
The best way is to disable all user access to tables.  Code up stored procs and give those procs access to tables. Give users execute access to procs to get the data.  I know... but you asked.

Back on this planet, you may want to consider using DSN-less connect strings (Google it).  You can programmatically connect from Access using such a connection without storing the login credentials in the DSN.

You can also store the DSN without login credentials and programmaticly append them to the connection string.  If a user tries to use that DSN through EXCEL or the like, ODBC will prompt for a userid/password.

Author Closing Comment

ID: 35755522
This is the best route for US..We haven't implemented it-But solves our problem.

Thank you...Any other information would be greatly helped..

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question