Do a search on the logins and switch the default database to master for each. Makes things so much easier.
I'm still receiving the same error as my post above , login failed although the command runs successfully
default databases are set to master already
Is it just login failed? Or is it login failed with cannot open database?
Did you actually unorphan the users or just generate a script? See Kristen's post regarding the EXEC and not just PRINT. You have to uncomment the EXEC and then run the whole thing again. Or alternatively, copy/paste/execute the PRINTed commands.
I'd suggest troubleshooting this by checking out the login's properties. I bet the default database is blanked out since the user is still orphaned.
How many logins do you have? I'd probably just start over and copy them over with their sids so that you don't have to worry about unorphaning them.
Drop the logins on your test server. TEST SERVER.
Go to the prod server and run this:
SET NOCOUNT ON
SELECT 'EXEC sp_addlogin @loginame = ''' + loginname + ''''
,', @defdb = ''' + dbname + ''''
,', @deflanguage = ''' + language + ''''
,', @encryptopt = ''skip_encryption'''
,', @passwd ='
, cast(password AS varbinary(256))
,', @sid ='
, sid
FROM syslogins
WHERE name NOT IN ('sa')
AND isntname = 0
ORDER BY loginname
SELECT 'EXEC sp_grantlogin @loginame = ''' + loginname + ''''
,' EXEC sp_defaultdb @loginame = ''' + loginname + ''''
,', @defdb = ''' + dbname + ''''
FROM syslogins
WHERE loginname NOT IN ('BUILTIN\Administrators')
AND isntname = 1
Copy/paste the output into the test server. If you see any logins that shouldn't be copied over, remove those from the pasted output. Then F5 it. Be sure you are doing this on the TEST SERVER.
1 login, user id = LAW_User Password=law32sql
as instructed in the very short guide here http://help.lexisnexis.com/litigation/ac/law/index.html?configuring_law_5_0_with_sql.htm
after running the code you instructed it produced this
as instructed I pasted it into the test server, completed successfully although nothing changed when trying to access a case
You'll need to switch that to text output instead of grid output. You need the entire row and not just the column. Click CTRL+T to switch to text mode and then rerun the command on production. Copy/paste the entire string and F5 it on the test server.
I wasn't using the admin account at the time I entered the code into the production server, Now I've found 2 other accounts which don't seem to be able to be recreated because they don't seem to have been able to be created. I'll update this as soon as I know more
I'm having trouble figuring out what's authenticating this account, is there perhaps a different way to see whats authenticating it?
There is no lexisnexis-law tree here. Nor has there ever been, yet its using a Windows authentication method and its baffling me. I may have to contact lexis nexis although I'd rather not if I don't have to
EDIT----
I do not believe at this time that my issue resides with those accounts, as I'm able to create a new case within law, as well as access the case afterwards, essentially saying everything is configured correctly.... Also, If I attach the databases one at a time I'm able to access those cases without this error I've been having.
What is causing me to believe that this is not a permission issue at this point, is that there is a side program that comes with law called management console which is the part of the program that manages multiple pc's performing the same task. Now when I attempt to connect to my sql running from within the test server it is not able to connect, it is not finidng the sql server (sidenote: the credentials it uses are LAW_User and I have verified LAW_User is the same on both machines)
I have verified the sql server browser service is started, the user account its using for my management console to connect is the LAW_User which I have verified is correct leading me to believe there has to be a piece that I'm missing thats allowing connections to the sql instance. I have also verified the LAW_User account has all required permissions within sql account properties
I'm not sure what you mean by "what's authenticating this account". That's a Windows account and might be the service account, perhaps an application account, perhaps an employee's account, etc. I'm not sure what you mean by tree either.
To see if the user is in use, you can check sp_who, the activity dashboard or any other means that shows current activity. I use sp_who or master.dbo.sysprocesses for a quick check. I use sp_WhoIsActive (you'd have to download and set it up) to see activity over time.
Also Windows account do not have the orphaned sid issue, so what I've been helping you with would not apply to this specific account. It applies to only SQL accounts.
At the moment I'm having issues connecting to my instance of SQL from outside of the machine. I'm going through the settings to see if I can find any differences.
The account LEXISNEXIS-LAW-\LawServerAcct is an account which would be set up under an active directory tree by the name of LEXISNEXIS-LAW- is how that appears to me, this is a Windows Account and our Domain is WI so accounts would be registered as WI\ACCOUNTNAME if they're being authenticated by Windows Authentication. These are the only 2 accounts which are different which may not be part of my problem although it is interesting.
I ran the code that was produced
EXEC sp_grantlogin @loginame = 'LEXISNEXIS-LAW-\Administrator' EXEC sp_defaultdb @loginame = 'LEXISNEXIS-LAW-\Administrator' , @defdb = 'master'
EXEC sp_grantlogin @loginame = 'LEXISNEXIS-LAW-\LawServerAcct' EXEC sp_defaultdb @loginame = 'LEXISNEXIS-LAW-\LawServerAcct' , @defdb = 'master'
EXEC sp_grantlogin @loginame = 'NT SERVICE\SQLWriter' EXEC sp_defaultdb @loginame = 'NT SERVICE\SQLWriter' , @defdb = 'master'
EXEC sp_grantlogin @loginame = 'NT SERVICE\Winmgmt' EXEC sp_defaultdb @loginame = 'NT SERVICE\Winmgmt' , @defdb = 'master'
EXEC sp_grantlogin @loginame = 'NT Service\MSSQL$LEXISNEXISLAWSVR' EXEC sp_defaultdb @loginame = 'NT Service\MSSQL$LEXISNEXISLAWSVR' , @defdb = 'master'
EXEC sp_grantlogin @loginame = 'BUILTIN\Users' EXEC sp_defaultdb @loginame = 'BUILTIN\Users' , @defdb = 'master'
EXEC sp_grantlogin @loginame = 'NT AUTHORITY\SYSTEM' EXEC sp_defaultdb @loginame = 'NT AUTHORITY\SYSTEM' , @defdb = 'master'
EXEC sp_grantlogin @loginame = 'NT SERVICE\ReportServer$LEXISNEXISLAWSVR' EXEC sp_defaultdb @loginame = 'NT SERVICE\ReportServer$LEXISNEXISLAWSVR' , @defdb = 'master'
although I had to remove the 2 users beginning with LEXISNEXIS-LAW of course.
beyond that the issue is still the exact same I've been posting, although this is where I'm noticing I'm unable to connect to that instance of sql outside of that machine leading me to think that may be part of the reason why I'm seeing this error still
Although maybe the 2 errors aren't related. I'm just trying to match up the settings and understand this well enough to recreate the SQL for our specific purpose to this program as well as keeping full functionality throughout the process
Is this SQL Server Express edition on the test server?
Does this script produce any output on prod:
SET NOCOUNT ON
SELECT 'EXEC sp_addlogin @loginame = ''' + loginname + ''''
,', @defdb = ''' + dbname + ''''
,', @deflanguage = ''' + language + ''''
,', @encryptopt = ''skip_encryption'''
,', @passwd ='
, cast(password AS varbinary(256))
,', @sid ='
, sid
FROM syslogins
WHERE name NOT IN ('sa')
AND isntname = 0
ORDER BY loginname
The above script is for the SQL logins. The other script was for the Windows logins.
this is sql express edition
and the output to the above script is:
EXEC sp_addlogin @loginame = '##MS_AgentSigningCertificate##' , @defdb = 'master' , @deflanguage = 'us_english' , @encryptopt = 'skip_encryption' , @passwd = NULL , @sid = 0x0106000000000009010000005180848BA37EE8AFE3242F190FBD91E6CB7585CD
EXEC sp_addlogin @loginame = '##MS_PolicyEventProcessingLogin##' , @defdb = 'master' , @deflanguage = 'us_english' , @encryptopt = 'skip_encryption' , @passwd = 0x0200C207A3A7CF222A50D7094BF440EE95C033510DF0425655CE42488F1E9845C4C5FE2B75354C423A4B06F70DACE185382B68CFB05EE91C70084115C899588D7D9668726906 , @sid = 0x8132F082E94B97439A075FED787F7A73
EXEC sp_addlogin @loginame = '##MS_PolicySigningCertificate##' , @defdb = 'master' NULL , @encryptopt = 'skip_encryption' , @passwd = NULL , @sid = 0x010600000000000901000000FFA801E471A92BDC749D3ED0A5AA2845B0A2E614
EXEC sp_addlogin @loginame = '##MS_PolicyTsqlExecutionLogin##' , @defdb = 'master' , @deflanguage = 'us_english' , @encryptopt = 'skip_encryption' , @passwd = 0x0200A53280E8586B753C484AD1A2673480F841E448165EBCDDB8594CD4D5F4D778269FB39B44E38B4AA53286BA27E267841D37D5CA5015C645ED3092DB55AC5B671FA5803496 , @sid = 0xFB5428192C68DE479445435923D3CE58
EXEC sp_addlogin @loginame = '##MS_SmoExtendedSigningCertificate##' , @defdb = 'master' NULL , @encryptopt = 'skip_encryption' , @passwd = NULL , @sid = 0x010600000000000901000000E2209E9F2C92438524C30A8020BF189A61769B5E
EXEC sp_addlogin @loginame = '##MS_SQLAuthenticatorCertificate##' , @defdb = 'master' NULL , @encryptopt = 'skip_encryption' , @passwd = NULL , @sid = 0x010600000000000901000000EEDFADC69D28C457F9FD5BDF7D7EB201033D8518
EXEC sp_addlogin @loginame = '##MS_SQLReplicationSigningCertificate##' , @defdb = 'master' NULL , @encryptopt = 'skip_encryption' , @passwd = NULL , @sid = 0x0106000000000009010000004BE0021C4421F7DF57837BE48827109E7ECBD8E7
EXEC sp_addlogin @loginame = '##MS_SQLResourceSigningCertificate##' , @defdb = 'master' NULL , @encryptopt = 'skip_encryption' , @passwd = NULL , @sid = 0x01060000000000090100000013D2FF4FD8AD2ED2A58623E6579F05A0C92D7DAC
EXEC sp_addlogin @loginame = 'Law_User' , @defdb = 'master' , @deflanguage = 'us_english' , @encryptopt = 'skip_encryption' , @passwd = 0x020051445A7E2DE80E0BCE53713AAA77B94A43D67413B035C158479C8AA79628F6F25701DDC67742E9EE505007F43956653D4B99A8185FD4BC2834E6DA7BA006C91D15DBF109 , @sid = 0x9A15642B7F76BF4F80FB904D586D593C
Express edition doesn't allow remote connections by default, so you'll have to enable it: https://blogs.msdn.microsoft.com/sqlexpress/2005/05/05/how-to-configure-express-to-accept-remote-connections/
This should resolve the remote access issue that you are having.
Now run this on the test server:
SELECT 'Login' AS principal_type,SP.name,SP.sid
FROM sys.server_principals AS SP
WHERE name = 'Law_User'
UNION ALL
SELECT 'User' AS principal_type,DP.name,DP.sid
FROM sys.database_principals AS DP
WHERE name = 'Law_User';
If the output shows that the SIDs are different, run this to fix them:
ALTER USER Law_User WITH LOGIN = Law_User;
Next run the above SELECT again to verify that the SIDs are now the same (if they were different to begin with).
1 Like
I'm to only run that on the test server not the prod server?
Yes just on the test server since the login and user already exist.
When I run the first command I get
Login LAW_User 0xE1F60C35232A8442BBB651732AF41E20
(1 row(s) affected)
running the second command gets me:
Msg 15151, Level 16, State 1, Line 1
Cannot alter the user 'Law_User', because it does not exist or you do not have permission.
I'm logged in as sa so I should most certainly have permission
Oh I meant to say run it in one of the databases that got attached.
If the SIDs were different, the ALTER will need to be done in each of the attached databases. We'll use sp_msforeachdb for that:
EXEC sp_MSforeachdb @command1='ALTER USER Law_User WITH LOGIN = Law_User;'
It'll error for any database where that user doesn't exist, such as master, model. It erroring in the system database is okay and expected.