BCM and SQL

Y

ylafont

I’m hoping that someone can point me in the right direction. I hav
moves the BCM database over the 2003 Small Business Sever withou
install BCM on the server. I have kept the instance the and databas
name the same. I am able to make a client odbc connection over to th
database and query it using excel. Which tell me the connection i
working, However, BCM on the client machine cannot see the database
I’m missing something
 
L

Luther

I assume the successful Excel connection is from the same BCM client
machine.

Was the BCM database shared before you moved it to the server? The
databases have to be shared before they'll appear in client's
connection wizard. If you can query the db direct through sql have a
look at the table named orgtable. I think one of those columns changes
when a database changes state between private and shared. You can
experiment, flip a db between private and shared, and examine the
orgtable each time.
 
Y

ylafont

I have tried to get this working for the past day without success; I
doesn’t seem that there is a smooth process to get this applicatio
working from a server. I have Done as you suggested and checked t
see if the application is in shared mode and added users to the list
In addition I have.

• Copied the database to the server with SQL Enterprise Manager over t
the server. Besides the errors received about the local users not bein
able to be migrated the copy process was successful.
• Changes between Windows and SQL and Windows Authentication.
• Changed the port Number between 1433 and 56183.
• Turned on the windows firewall and opened both of the above ports o
both machines. (saw a tech noted on this)
• Create an ODBC connection and queried it from excel on the clien
machine to assure me the connection is valid.

There has to be something that I’m missing. Has anyone gotten this t
work
 
L

Luther

BCM only works with Windows auth, with the same user on both client and
server. If Excel connects, that user's got permission to access the
database.

BCM is hard coded to access the sql instance via TCP port 56183. UDP
1433 is used by default sql instances for clients to find the
instance's current port and BCM does not use it. In a default
installation, BCM turns it off.

When you connect Excel via ODBC did you specify/require 56183, or just
the instance name? If you didn't, then your instance is probably using
the deault config.

BCM will only work if the instance has been configured to use that port.
 
Y

ylafont

I have done so many changes, I don’t know the current settings, I’
going to go back and make sure of the following before I continue.

Windows authentication works.
The port is set to 56183 on both the server and client.
ODBC connections works with windows authentication.
And that excel can use the connection on the client side with ou
problems.

I have noticed that ownership of the client database change to anothe
user when it is moved to the server with enterprise manager, Is tha
normal? Although the client can still connect to the local database
it can no longer be managed.

Also, what is the purpose of turning on the firewall?


By the way, thanks for all your help
 
L

Luther

I believe the owner of the database will be the user that restores it
onto the new server.

Turning on the firewall is done automatically by XP SP2. Then BCM opens
the TCP port so that its data can travel through.

Again, when you connect successfully with ODBC/Excel, are you
specifying just the server, or including the port number in the Data
Source? If not, then your SQL instance is probably using UDP to
advertise its TCP port number and it won't be 56183. BCM will only work
if the instance is using 56183.
 
Y

ylafont

In response to your question in excel, I have preformed the connectio
both ways. By connecting with just the server name and selecting th
database; and by changing the port number in the client configuration
tab. I have also accomplished the connection by using SQ
authentication (even thought i know BCM does not use that type o
authentication) just to make sure that the connection was valid an
working.

I will be trying this again between today and tomorrow just to confir
everything
 
Top