?
Solved

Creating an MS SQL Server user for MS Access connectivity

Posted on 2012-09-16
5
Medium Priority
?
504 Views
Last Modified: 2012-10-02
I created a user in MS SQL Server 2008 Express via:
sp_addlogin 'bejhan', 'password', 'nsf'
sp_addrolemember N'db_datareader', N'bejhan'
p_addrolemember N'db_datawriter', N'bejhan'

Open in new window


I then set the connection string of my table definitions to:
ODBC;DRIVER=SQL Server;SERVER=BEJHAN-LAPTOP\SQLEXPRESS;DATABASE=nsf;UID=bejhan;PWD=password

Open in new window


However, when I try to refresh the table links I get the following error:
Connection failed:
SQLState: '28000'
SQL Server Error: 18456
[Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for user 'bejhan'.

Open in new window


What am I missing?
0
Comment
Question by:bejhan
  • 2
  • 2
5 Comments
 
LVL 5

Accepted Solution

by:
sameer_goyal earned 2000 total points
ID: 38403137
did you create the login (user) before you executed the 3 commands above?

i guess this is what you need. just the change the respective details as per your requirement

USE [master]
GO
CREATE LOGIN [test] WITH PASSWORD=N'test', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
EXEC master..sp_addsrvrolemember @loginame = N'test', @rolename = N'sysadmin'
GO
0
 
LVL 1

Author Comment

by:bejhan
ID: 38403584
I forgot include that after I executed sp_addlogin, I used sp_adduser:

sp_addlogin 'bejhan', 'password', 'nsf'
sp_adduser 'bejhan, 'bejhan'
sp_addrolemember N'db_datareader', N'bejhan'
p_addrolemember N'db_datawriter', N'bejhan'

Open in new window

But based on Sameer's comment it seems I missed setting the sysadmin role.

I'll try that later today.

Jamie, I am not actually using "password" as the password, I've just replaced the real password for posting.
0
 

Expert Comment

by:Jamieo1
ID: 38403731
I think you need to use Sameer's code - create Login, not sp_addlogin as:

"This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use CREATE LOGIN instead."

And specify the check_policy as off (defaults to on) with create login

CHECK_POLICY = { ON | OFF }

    Applies to SQL Server logins only. Specifies that the Windows password policies of the computer on which SQL Server is running should be enforced on this login. The default value is ON.

    If the Windows policy requires strong passwords, passwords must contain at least three of the following four characteristics:

        An uppercase character (A-Z).

        A lowercase character (a-z).

        A digit (0-9).

        One of the non-alphanumeric characters, such as a space, _, @, *, ^, %, !, $, #, or &.
0
 
LVL 1

Author Comment

by:bejhan
ID: 38431226
Sorry guys, lost all of my time to another project. I'll give your suggestions a try in a few days.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
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.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

839 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