bejhan
asked on
Creating an MS SQL Server user for MS Access connectivity
I created a user in MS SQL Server 2008 Express via:
I then set the connection string of my table definitions to:
However, when I try to refresh the table links I get the following error:
What am I missing?
sp_addlogin 'bejhan', 'password', 'nsf'
sp_addrolemember N'db_datareader', N'bejhan'
p_addrolemember N'db_datawriter', N'bejhan'
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
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'.
What am I missing?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
http://technet.microsoft.com/en-us/library/ms161959%28v=sql.100%29.aspx
ASKER
I forgot include that after I executed sp_addlogin, I used sp_adduser:
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.
sp_addlogin 'bejhan', 'password', 'nsf'
sp_adduser 'bejhan, 'bejhan'
sp_addrolemember N'db_datareader', N'bejhan'
p_addrolemember N'db_datawriter', N'bejhan'
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.
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 &.
"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 &.
ASKER
Sorry guys, lost all of my time to another project. I'll give your suggestions a try in a few days.