Sims SA user problem

[MattGibson]

Very strange problem i went to do a backup last night but couldn't get the SA user to work. so i checked nightly backup script to see what password it was using, and sure enough it was correct. So how comes its backing up with SA user but im unable to use it under DBAttach....?

 

Any ideas?

[matt40k]

Re-run S:\setups\SIMSSQLApplicationSetup.exe

 

Check where it installs too, some times it refers to the old path (MSDE).

There are known issues with dbattach with switches.

[matt40k]

Try running this code against the database (at your own risk, of course) from a query in SQL Manager.

 

use [your_database_name]

exec sims.db_p_install

 

This should fix the users / logins.

 

That won't fix it. SA isn't a SIMS user account, and it isn't an sa issue.

 

Handy to know tho.

[MattGibson]

the exact message i get is "Login Failed for user 'sa'."

 

Cant really understand if its backing up with sa - surely it should work for other database tasks such as dbattach

[MattGibson]

How do i do this as sa isn't part of the system manager.

 

Do i use ChSaPwd.exe?

[matt40k]

Log on to the SIMS\MSSQL server as administrator and run...

 

OSQL -E -S servername -Q "exec sp_password NULL, 'newpassword', 'sa'"

[MattGibson]

Ah Brilliant, Cheers for that it worked - My SQL knowledge is very limited.

 

Don't understand how it could use the same login on the script to do a backup. but wouldn't let me use it anywhere else.

[matt40k]

You mean MSSQL [Quickly covers up his documents with MySQL instend of Mssql]

 

I would try reinstalling dbattach.

[MattGibson]

Me again!

 

Since changing the sa password my backups are now failing, When the SQL Script runs it says failed login for Sims & FMS.....

 

I have looked at the script under -Usa -Ppassword and its is the same password as i changed it too the other week.

 

Is there another table in the database which needs updating?

 

cheers guys

[theriver]

Have you got multiple instances of SQL going on there?

 

Is the instance name in the backup script the same one that you specified in the password change command, i.e.

 

-S servername

 

vs

 

-S servername\instancename

 

Also - am assuming that you are running the commands from the same machine that is running the backup script, is that correct?

[MattGibson]

Yep Double checked its the same as what i used.

 

OSQL -E -S ADM-Sr-001\Sims2005 -Q "exec sp_password NULL, 'newpassword', 'sa'"

 

And this seemed to work.

 

Like i said before the backup script worked fine before i changed the sa password because i was unable to use DBAttach.

 

The script I'm using is:

 

OSQL -S ADM-Sr-001\Sims2005 -E -N -Q "exec ccsfms.sims.db_p_transfer_login"

OSQL -S ADM-Sr-001\Sims2005 -E -N -Q "exec sims.sims.db_p_transfer_login"

OSQL -S ADM-Sr-001\Sims2005 -Usa -Ppasswordsql-2005 -n -Q "BACKUP DATABASE SIMS TO DISK = 'F:\Program Files\Microsoft SQL Server 2005\MSSQL.1\MSSQL\Nightly\SIMSSQL.BAK'"

OSQL -S ADM-Sr-001\Sims2005 -Usa -Ppasswordsql-2005 -n -Q "BACKUP DATABASE CCSFMS TO DISK= 'F:\Program Files\MICROSOFT SQL Server 2005\MSSQL.1\MSSQL\NIGHTLY\CCSFMS.BAK'"

net stop mssql$sims2005

net start mssql$sims2005

[matt40k]

:S

 

Why are you stopping the service and starting it? Backups like this are done live

 

 

 

I would run a query (using SQL Server Management Studio ) to check for any errors.

 

DBCC CHECKDB WITH NO_INFOMSGS, TABLERESULTS

 

 

Check there is no errors in the database. I would really look at doing this via dbattach with switches.

 

Otherwise, just stop the service then copy the .mdf and .ldf

[MattGibson]

:S

 

Why are you stopping the service and starting it? Backups like this are done live

 

 

 

I would run a query (using SQL Server Management Studio ) to check for any errors.

 

DBCC CHECKDB WITH NO_INFOMSGS, TABLERESULTS

 

 

Check there is no errors in the database. I would really look at doing this via dbattach with switches.

 

Otherwise, just stop the service then copy the .mdf and .ldf

 

Thats who set it up before me, I thought that was a bit strange. Will CheckDB later. i think our DB needs a good sorting out......

[penfold_99]

Yep Double checked its the same as what i used.

 

OSQL -E -S ADM-Sr-001\Sims2005 -Q "exec sp_password NULL, 'newpassword', 'sa'"

 

And this seemed to work.

 

Like i said before the backup script worked fine before i changed the sa password because i was unable to use DBAttach.

 

The script I'm using is:

 

OSQL -S ADM-Sr-001\Sims2005 -E -N -Q "exec ccsfms.sims.db_p_transfer_login"

OSQL -S ADM-Sr-001\Sims2005 -E -N -Q "exec sims.sims.db_p_transfer_login"

OSQL -S ADM-Sr-001\Sims2005 -Usa -Ppasswordsql-2005 -n -Q "BACKUP DATABASE SIMS TO DISK = 'F:\Program Files\Microsoft SQL Server 2005\MSSQL.1\MSSQL\Nightly\SIMSSQL.BAK'"

OSQL -S ADM-Sr-001\Sims2005 -Usa -Ppasswordsql-2005 -n -Q "BACKUP DATABASE CCSFMS TO DISK= 'F:\Program Files\MICROSOFT SQL Server 2005\MSSQL.1\MSSQL\NIGHTLY\CCSFMS.BAK'"

net stop mssql$sims2005

net start mssql$sims2005

 

You could do this under a scheduled maintenance plan in SQL Server Management Studio.

[MattGibson]

:S

 

DBCC CHECKDB WITH NO_INFOMSGS, TABLERESULTS

 

Reported no errors. Only took about 5 Seconds!

[matt40k]

At least your DB isn't corrupt. I would contact Capita\SIMS Support team about getting DBATTACH fixed.

 

Then either, to backup:

 

Use DBATTACH

Use SQL Server Management Studio (assuming you have full SQL)

Stop the service, copy the MDF\LDF and start the service.

[MattGibson]

2008-11-20 13:46:35.76 Logon Login failed for user 'sa'. [CLIENT: 10.18.52.5]

[matt40k]

Download nmap and check the ports open.

[MattGibson]

The script we use is provided by our LEA, I have resolved this issue by removing the -U -P . Everything back to normal now