March 9, 2009 at 4:30 am
We have migrated our Database to a new server. All is well apart from one Database where the front end application is unable to login to the database.
It is a SQL Login and I have deleted and recreated the login on the new server, to ensure that there is no question is an orphan.
The error is
provider=SQLOLEDB;Data Source=ServerName;Initial Catalog=DatabaseName;UID=Login;Pwd=Password;
Login failed for user 'Login'
Have checked the Login and Password most carefully.
Any comments most welcome! 😉
Thanks
Colin
March 9, 2009 at 5:22 am
March 9, 2009 at 5:36 am
Thanks,
Nothing returned.
I have run a script, I found on the web, to identify orphan users. But nothing was returned there either.
Colin
March 9, 2009 at 6:02 am
To get your inventory of which db-users to synchronize, you can use this script:
You need to run this script in every database of the sql instance !!!
print 'print @@servername + '' / '' + db_name()'
print 'go'
go
declare @username varchar(128)
declare @Musername varchar(128)
declare @IsNtName bit
declare @sql_stmt varchar(500)
declare @ExcludeWindowsAccounts Char(1)
set @ExcludeWindowsAccounts = 'N' -- set to 'Y' if windows accounts need not be handled !
--cursor returns with names of each username to be tied to its respective
DECLARE user_cursor CURSOR FOR
SELECT su.name as Name, msu.name as MasterName , su.isntname
FROM sysusers su
left join master.dbo.sysxlogins msu
on upper(su.name) = upper(msu.name)
WHERE su.sid > 0x00
ORDER BY Name
--for each user:
OPEN user_cursor
FETCH NEXT FROM user_cursor INTO @username, @Musername, @IsNtName
WHILE @@FETCH_STATUS = 0
BEGIN
IF @username NOT IN ('dbo', 'list of names you want to avoid')
BEGIN
if @Musername is null
begin
if @IsNtName = 1
begin
if @ExcludeWindowsAccounts = 'N'
begin
print 'if not exists (select * from master.dbo.syslogins where loginname = N''NtDomein**\' + @username + ''')'
print ' begin '
print 'exec sp_grantlogin N''NtDomein**\' + @username + ''''
print 'exec sp_defaultdb N''NtDomein**\' + + @username + ''', N'''+ db_name() + ''''
print ' end'
set @sql_stmt = '--Windows account gehad'
end
else
begin
set @sql_stmt = '--'
end
end
else
begin
SELECT @sql_stmt = 'sp_change_users_login @Action = ''Auto_Fix'',@UserNamePattern = ''' + @username + ''''
end
end
else
begin
SELECT @sql_stmt = 'sp_change_users_login @Action = ''Update_One'',@UserNamePattern = ''' + @username + ''', @LoginName = ''' + @username + ''''
end
PRINT @sql_stmt
print 'go'
print '--*** : exec stmt commented !!! ***'
--EXECUTE (@sql_stmt)
END
FETCH NEXT FROM user_cursor INTO @username, @Musername, @IsNtName
END --of table-cursor loop
--clean up
CLOSE user_cursor
DEALLOCATE user_cursor
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 9, 2009 at 6:21 am
I am assuming this is the Orphan users script that you used:
USE DatabaseName
GO
EXEC sp_change_users_login 'Auto_Fix', 'LoginName'
GO
Can you login to the Server using the credentials via an ODBC DSN?
March 9, 2009 at 7:29 am
just quickly does the login have the correct default database................
---------------------------------------------------------------------
March 9, 2009 at 7:35 am
Yes, it has the correct default Database.
It cannot be an orphan as the login has been deleted and recreated on the server and in the Database.
The login cannot access tthe Database.
All very strange.
March 9, 2009 at 7:47 am
have you set the security audit level to 'failure' for the instance? If not do so (will require a SQL restart I'm afraid). Look up the error 18456 messages in the log and see what the 'state' value is set to. That will tell you the cause of the login failure.
Can't find a good link which describes all the explanations for 'state' right now.....
Is this a migration up to SQL 2005? If so it could be because the password does not fit in with the password policy and the login has been locked out.
---------------------------------------------------------------------
March 9, 2009 at 3:15 pm
ehm .... tcp/ip enabled for that sql-instance ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 10, 2009 at 2:08 am
We have several other Databases on the server and those applications all work well.
It is a straight migration between servers keeping SQL 2000
Changing the initial application login to a new login gives us access and so we are going to change the code for the half dozen or so queries, to that alternative login.
Just a single login, created on the new server and allocated to the database on the new server and it fails. Another login, treated in the same way works.
Thanks for the support, appreciated
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply