Connecting non domain user to domain SQL BCM 2007

[Ian_m]

We have successfully installed and are using SQL Express 2005 on a domain
account on a server and domain users are using BCM fine on their machines.
The database admin tool offers domain accounts to be added to sharing list,
all works fine for domain logged on users.

However we have two machines who are domain connected computers (and have
domain computer accounts) but due to legacy reasons the users are logging on
as local machine accounts rather than domain accounts. Outlook 2007 with
Exchange 2007 works fine for these users.

However the BCM 2007 Database admin tool offers no way to add
workgroup/local machine accounts to list of database users.

I have tried adding SQL authenticated accounts to the SQL database (using
Microsoft SQL Server Management Studio Express), but it appears the users
account information is being refused by the server before even being passed
to SQL server (ie nothing in the SQL logs). From BCM2007 I get "Cannot find
the remote user account name xxxx xxxx".

Any thoughts ? I don't want to really convert the machine accounts to domain
accounts (with all the time and hassle and downtime that incurrs) as
currently everything works fine for the users except unable to connect to SQL
server.

 

[Luther]

We have successfully installed and are using SQL Express 2005 on a domain
account on a server and domain users are using BCM fine on their machines.
The database admin tool offers domain accounts to be added to sharing list,
all works fine for domain logged on users.

However we have two machines who are domain connected computers (and have
domain computer accounts) but due to legacy reasons the users are logging on
as local machine accounts rather than domain accounts. Outlook 2007 with
Exchange 2007 works fine for these users.

However the BCM 2007 Database admin tool offers no way to add
workgroup/local machine accounts to list of database users.

I have tried adding SQL authenticated accounts to the SQL database (using
Microsoft SQL Server Management Studio Express), but it appears the users
account information is being refused by the server before even being passed
to SQL server (ie nothing in the SQL logs). From BCM2007 I get "Cannot find
the remote user account name xxxx xxxx".

Any thoughts ? I don't want to really convert the machine accounts to domain
accounts (with all the time and hassle and downtime that incurrs) as
currently everything works fine for the users except unable to connect to SQL
server.

I know that BCM and its database is built to use Windows
Authentication, never Sql accounts, and also that the sharing is
expected to be in a domain or workgroup, and not a mix of the two, and
that some features in BCM work differently in each mode. But it may be
possible as long as the database is happy accepting connections from
workgroup users.

I would try to have your workgroup users connect to the BCM database
using osql, to keep BCM out of the way. If you can figure out how to
connect them with osql using Windows Authentication, then try to
attach to that database from BCM using the same Windows login.

Even if you get it to work, there's a possibility that the next time
the DBO runs the BCM sharing wizard--e.g. to add a new user--the BCM
code may drop any login it doesn't recognize in the domain from the
list of shared users.

 

[Ian_m]

I know that BCM and its database is built to use Windows
Authentication, never Sql accounts, and also that the sharing is
expected to be in a domain or workgroup, and not a mix of the two, and
that some features in BCM work differently in each mode. But it may be
possible as long as the database is happy accepting connections from
workgroup users.

I would try to have your workgroup users connect to the BCM database
using osql, to keep BCM out of the way. If you can figure out how to
connect them with osql using Windows Authentication, then try to
attach to that database from BCM using the same Windows login.

Even if you get it to work, there's a possibility that the next time
the DBO runs the BCM sharing wizard--e.g. to add a new user--the BCM
code may drop any login it doesn't recognize in the domain from the
list of shared users.

Right after much head scratching we have cracked it.

In summary issues were:-

BCM users on domain accounts connect to BCM SQL fine, can be seen in SQL log
connecting and BCM works for them.

However we have a couple of users whose machines are domain computers but
the accounts they use are local machine accounts. This is because of legacy
reasons, these users were upgraded from Windows 95/98 -> XP and Outlook 98 ->
Outlook 2000 around year 2000-2001 4 years before we got a domain. They
connect to domain shares fine, obviously using the XP stored network username
and password. However their local account user name (and password) eg "Fred
Bloggs" does not match their domain account "Fred_B" (and password).

When they attempt to connect to the BCM SQL database they get SQL server not
found error messages and we see nothing in the SQL log, they are not getting
as far as even attempting to connect to the SQL server (a big clue !!!).

I suspect as the Guest account is disabled on our SQL server machine they
can't make the initial connection.

The fix is to create another domain user account matching the user name and
password of the local machine account.

An bingo, hey presto ta dah...they connect fine. One can see (for example)
"Fred Bloggs" connecting and being authorised to the server (via Security
Event log) and then connecting to SQL server as "Fred_B" in the SQL log.

The MS documentation is completely right on this in the trouble shooting
database section, "make sure the machine containing the SQL server has a user
account and password exactly the same as the account you are trying to
connect from and you are uathorised to access the SQL server".

Users all happy, machine has not changed as far as they are concerned and
not major fiddling required by me (apart from Outook 2007 being differerent
than Outlook 2000).

Wonder what the CAL licencing implications are of have a single user
connecting to a domain using two domain accounts ?