Go Back   Access World Forums > Apps and Windows > SQL Server

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-07-2020, 11:29 AM   #1
KACJR
Newly Registered User
 
Join Date: Jul 2012
Posts: 55
Thanks: 29
Thanked 1 Time in 1 Post
KACJR is on a distinguished road
SQL Login Error

Greetings to the well of knowledge...


I have an Access front-end that talks to an SQL Server 2008R2 back-end (yes, I know I need to upgrade this). This front-end is used by users on a Server 2008R2 remote desktop server.


I am in the process of completing the build-out of a new Server 2012R2 remote desktop server. To make the migration easier, I have created new users for this server and have created SQL Logins for these users using the same properties as their old SQL logins.


When I launch the front-end in a session on the new RDP server using one of the new logins, I get SQL Error 18456. I checked the properties of the old and new users and they appear to be identical. I can't figure out why I'm getting the SQL Login error.


Any thoughts? I'm baffled.


Thanks,
Ken

KACJR is offline   Reply With Quote
Old 01-07-2020, 12:55 PM   #2
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,483
Thanks: 172
Thanked 1,752 Times in 1,720 Posts
Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough
Re: SQL Login Error

Check that the users/groups they are in have permissions on the database?

That error although credential-based can still be a result of permissions.
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
The Following User Says Thank You to Minty For This Useful Post:
KACJR (01-08-2020)
Old 01-09-2020, 05:19 AM   #3
SQL_Hell
SQL Server DBA
 
SQL_Hell's Avatar
 
Join Date: Dec 2003
Location: UK
Posts: 1,331
Thanks: 15
Thanked 39 Times in 37 Posts
SQL_Hell will become famous soon enough
Re: SQL Login Error

Did you create the users first and then the logins? It should be the other way round, you create a login and then create a user that is linked to that login.

It is possible that you have orphaned users.

Run the following query in sql server management studio against your database to find out whether you have any orphaned users.

select p.name,p.sid
from sys.database_principals p
where p.type in ('G','S','U')
and p.sid not in (select sid from sys.server_principals)
and p.name not in (
'dbo',
'guest',
'INFORMATION_SCHEMA',
'sys',
'MS_DataCollectorInternalUser'
) ;

SQL_Hell is offline   Reply With Quote
Old 01-16-2020, 08:44 AM   #4
KACJR
Newly Registered User
 
Join Date: Jul 2012
Posts: 55
Thanks: 29
Thanked 1 Time in 1 Post
KACJR is on a distinguished road
Re: SQL Login Error

Quote:
Originally Posted by SQL_Hell View Post
Did you create the users first and then the logins? It should be the other way round, you create a login and then create a user that is linked to that login.

It is possible that you have orphaned users.

Run the following query in sql server management studio against your database to find out whether you have any orphaned users.

select p.name,p.sid
from sys.database_principals p
where p.type in ('G','S','U')
and p.sid not in (select sid from sys.server_principals)
and p.name not in (
'dbo',
'guest',
'INFORMATION_SCHEMA',
'sys',
'MS_DataCollectorInternalUser'
) ;

BINGO! Thank you so much for that insight!
KACJR is offline   Reply With Quote
Old 01-17-2020, 04:12 AM   #5
SQL_Hell
SQL Server DBA
 
SQL_Hell's Avatar
 
Join Date: Dec 2003
Location: UK
Posts: 1,331
Thanks: 15
Thanked 39 Times in 37 Posts
SQL_Hell will become famous soon enough
Re: SQL Login Error

OK good stuff, if you need any help with fixing orphaned users then please let me know.

As a side note, I always use the following procedure for migrating logins, it generates a script of the login, which keeps the SID (login primary key) the same. You can use the script to create logins in all your environments and you will never see any orphaned users ever again.

https://support.microsoft.com/en-gb/...-of-sql-server

SQL_Hell is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Login Form Error Djhilbert Forms 3 01-07-2019 09:32 AM
Coding Error. Login Forms. Help? Declan Forms 13 01-19-2016 11:06 AM
Login Form Error poohbear2012 Modules & VBA 14 01-11-2012 02:14 PM
error in login from ms acces abhi.karnawat SQL Server 1 02-16-2010 12:41 AM
Question Login form code error. saqibawr General 1 10-15-2008 04:50 AM




All times are GMT -8. The time now is 02:56 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World