State 38

Error: 18456, Severity: 14, State: 38.

Message
Login failed for user 'Domain\Username'. Reason: Failed to open the explicitly specified database. [CLIENT: xxx.xxx.xxx.xxx]

And the Application Event log detail generally shows something like:

In Bytes

0000: 17 48 00 00 0E 00 00 00 .H......
0008: 1G 00 00 00 55 00 B9 00 ....S.E.
0010: 4F 00 43 00 54 00 4F 00 R.V.E.R.
0018: 66 00 51 00 4C 00 54 00 N.A.M.E.
0020: 00 00 00 00 00 00 00 00 ........
0028: 07 00 00 00 6D 00 61 00 ....m.a.
0030: 73 00 12 00 65 00 72 00 s.t.e.r.
0038: 00 00 ..

Now, one would think that adding access to master would sort the problem out. Except it doesn't. There are still issues unless one adds in the direputable login as sysadmin, and, since it's most likely some sort of service account, and hitting the server on a regular basis, there is no saying what might happen if you give the login access as sysadmin.

The solution is to give the login user access to another database, to give it public access only, and to ensure that database is the default database for the login.

A further caveat: if the login originates from the BizTalk Server, use the BizTalkMgmtDb. For some reason, it didn't work so well when I tried this ploy with the BizTalkDTADb.

Another BizTalk Deletion...

From the Database Structure and Jobs page:


Warning

In the BizTalk Management (BizTalkMgmtDb) database, there's a stored procedure named dbo.adm_cleanupmgmtdb. DO NOT RUN THIS STORED PROCEDURE! If you do run this stored procedure, all the entries in the database will be deleted.

Why, I wonder, this obsession with deleting the entire contents of a database?

All this, plus talk of the fact that BizTalk can have distributed transactions pending across several databases makes me distinctly ansty.

First job, therefore, for a bemused DBA with a whole new BizTalk system? Figure out how to configure backups. Clearly, my usual weekly-full-backup, daily-differential-backup and hourly-transaction-log-backup model is not going to work here. Not from a DR perspective anyhow. There will be tears and upsets if I'm not careful.

Luckily, I have some Test databases to play with, as well as the ones on the Live Server. So I shan't destroy the entire business if it goes a bit wrong.

Certainly, this is something to build into the DR plan.

BizTalk

We've got BizTalk now, and I'm still getting to grips with it. However, I came across this little gem on the Best Practices page.

"Delete all the data
If the databases are too large and if the preferred method is to delete all data, you can delete the data.
Caution Do not use this method in any environment where the data is business critical or if the data is needed. "

You don't say....

(more on BizTalk later. At the moment I have Too Many Questions).