Cannot connect to shared BCM database from excel

[isogulaleb]

Hello,

I have a shared BCM database (ver 2007) which is used by two users; no
problem with shared use.

I made an excel file with a link to the shared database (connecting to
PublicContactView) without problem - I can refresh data from my computer
(where the database is located) with ease.

However, when my shared user tries to open this excel file from his own
computer (where outlook w/ BCM works properly with shared data) and tries to
refresh connected data, he receives a message SQL Server does not exist or
access denied. For a quick check I tried to establish a new connection from
his computer from a brand new excel file but when I try to establish the
connection I receive the same error message.

It seems to me that he can use the shared database only through outlook and
cannot access my SQL server instance from his computer. Again, he is a shared
user in the database.

Is there any way to resolve this problem?

Thanks for any suggestion in advance.

 

[Joe]

Hello,

I have a shared BCM database (ver 2007) which is used by two users; no
problem with shared use.

I made an excel file with a link to the shared database (connecting to
PublicContactView) without problem - I can refresh data from my computer
(where the database is located) with ease.

However, when my shared user tries to open this excel file from his own
computer (where outlook w/ BCM works properly with shared data) and tries to
refresh connected data, he receives a message SQL Server does not exist or
access denied. For a quick check I tried to establish a new connection from
his computer from a brand new excel file but when I try to establish the
connection I receive the same error message.

It seems to me that he can use the shared database only through outlook and
cannot access my SQL server instance from his computer. Again, he is a shared
user in the database.

Is there any way to resolve this problem?

I'm sure there is, but I haven't had time to track it down yet. It's
Vista, isn't it? I'm using Access to talk to the BCM on a server, and it
works fine from XP but not from Vista. Presumably the firewall, which XP
doesn't have outbound. Or the ODBC connection, which I've tried setting
up while running as admin, but maybe it needs an actual admin logon.
Vista's fairly good like that, but I've found one or two places where an
admin logon is necessary.

It may be a week or two before I can deal with this, so post back if you
fix it before I do.

 

[isogulaleb]

Hello Joe,
Thanks for taking time. No, I am using xp on both computers. We have a
domain, firewall is managed by RRAS from domain group policy. I also tried to
specify admin name and passw at login but it seems that from that comp I
cannot reach the MSSMLBIZ instance on this machine. The server log on my
machine says Error: 18456, Severity: 14, State: 16. I tried to google it but
all I found was it is a permission problem.

Thanks for trying, I will post here if manage to solve before you do.

 

[isogulaleb]

Thanks for your time. I found an error in the log Error: 18456, Severity: 14,
State: 16. It seems a permission error but I did not find any details on the
net. I have XP on both machines. We have an SBS server, firewall is managed
by domain group policy.

I will post here if I find the solution earlier than you.

Thanks again.

 

[Luther Blissett]

Thanks for your time. I found an error in the log Error: 18456, Severity:14,
State: 16. It seems a permission error but I did not find any details on the
net. I have XP on both machines. We have an SBS server, firewall is managed
by domain group policy.

I will post here if I find the solution earlier than you.

Thanks again.






- Show quoted text -

Are you on the same machine as the db, but the other shared user is on
a different machine? If so, you propbably need to specify the server
(your machine) and/or the port number in the connection string.

 

[isogulaleb]

Thanks Luther for taking time to answer.

I tried to specify port 5356 as well but I am not able to make connection to
the SQL instance running BCM database from excel. I checked the firewall
settings and 5356 is open to local network. Also, SQL server configuration
says IPAll Port 5356. Everything should be fine.

Here is what I do from excel 2007 (maybe the terms are not exact as I use
Hungarian excel):

Data menu/Other Source/SQL server

There:

Server name: \\<Computer name running BCM database>\MSSMLBIZ
Authentication: Windows Authentication

Again, the user has permissions to use the BCM database, he can work with
the database through his outlook without problem. I tried MSSMLBIZ:5356,
MSSMLBIZ:56183, tried specifying direct local IP address instead of computer
name, nothing worked.

Grrr, this is getting to be annoying... thanks for your efforts!

 

[Luther Blissett]

Thanks Luther for taking time to answer.

I tried to specify port 5356 as well but I am not able to make connectionto
the SQL instance running BCM database from excel. I checked the firewall
settings and 5356 is open to local network. Also, SQL server configuration
says IPAll Port 5356. Everything should be fine.

Here is what I do from excel 2007 (maybe the terms are not exact as I use
Hungarian excel):

Data menu/Other Source/SQL server

There:

Server name: \\<Computer name running BCM database>\MSSMLBIZ
Authentication: Windows Authentication

Again, the user has permissions to use the BCM database, he can work with
the database through his outlook without problem. I tried MSSMLBIZ:5356,
MSSMLBIZ:56183, tried specifying direct local IP address instead of computer
name, nothing worked.

Grrr, this is getting to be annoying... thanks for your efforts!






- Show quoted text -

In my experience, you need to specify the instance connecting locally
(on same machine) and the port connecting remotely - since the port is
tied to the instance, the instance name is irrelevant when specifying
the port.

I would try:
Server name: \\<Computer name running BCM database>,5356

I'm not that familiar connecting from Excel and ODBC, but with osql
the format is host-comma-portnumber.

 

[isogulaleb]

Thanks Luther but still does not work; I tried \\comptname,5356,
\\comptname\instance,5356, even tried to do the aboves specifying the
credentials of the database creator and owner (myself).

Do you have a shared BCM database? If yes, would you do me the favour to
check this? Simply open a new excel sheet on a computer which is connecting
to BCM (but not running it) and go to data\other sources\SQL server and try
to make a connection? I would appreciate if someone could test this.

Thanks for helping me anyway.

 

[isogulaleb]

I managed to solve it. Anybody, who wants to connect to a shared BCM database
FROM EXCEL, do not write \\ BEFORE COMPUTER NAME - so, if your computer name
is PUPPY then you can connect from excel by

PUPPY,5356

Luther was right, specifying port is a must.

Luther, you are the hero of the day - I was fighting with this for more than
a week! Thanks again

 

[Luther Blissett]

Thanks Luther but still does not work; I tried \\comptname,5356,
\\comptname\instance,5356, even tried to do the aboves specifying the
credentials of the database creator and owner (myself).

Do you have a shared BCM database? If yes, would you do me the favour to
check this? Simply open a new excel sheet on a computer which is connecting
to BCM (but not running it) and go to data\other sources\SQL server and try
to make a connection? I would appreciate if someone could test this.

Thanks for helping me anyway.








- Show quoted text -

trafalmadore,5356 worked for me. No slashes.

It didn't work the first time, but then I opened TCP port 445in the
firewall, and set that and the Microsoft Small Business exception
(port 5356) to be open to the entire internet, on the database
machine. Being that open isn't very secure, but it proves Excel can
connect if it can reach the bcm database.