Local connection doesn't work

Question

Post reply

Local connection doesn't work

Gourou AWK

Valued Member

Points: 62
More actions
March 8, 2011 at 9:04 am

#142384

Hello everybody,
I experienced a problem I don't understand.
I've a local server SQL2005 on my laptop.
On another side I've an Excel 2007 file with following connection
Description=BNMO (PRODUCTION) COMPER DB;DRIVER=SQL Server;SERVER=BLLX300032028\JRINST;UID=romain_j;APP=2007 Microsoft Office system;WSID=BLLX300032028;DATABASE=APRF_DBSA;Trusted_Connection=Yes
Behind this connection several pivot tables are linked and they are refreshed on file open.
When my computer is not plugged on network, I can manage the complete database using SQL Server Management Studio but I can't access database from Excel file (Time out)
Behaviour is the same using SQL EXPRESS 2005
Any Idea?

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Orlando Colamatteo SSC Guru Points: 182293 More actions · Answer 1

Are you using a domain account or a local Windows account?

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Gourou AWK Valued Member Points: 62 More actions · Answer 2

I'm using a Domain account managed outside over the network. But there is a local cache for this information. What I don't understand, is why my connection through SQL management studio using the same account works fine, but not the XL 2007 connection.

Moreover, I discover yesterday that I can't access "Data Base Diagrams" SQL MS function when I'm disconnected. The "strange" message I received is :

"Database Diagram objects cannot be installed because this database does not have a valid owner. To continue, First use the Files pages of the database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support object"

Orlando Colamatteo SSC Guru Points: 182293 More actions · Answer 3

First issue: I think the reason why you cannot access the DB from Excel using Windows Auth while your machine is unplugged from the network is because SQL Server cannot reach an Active Directory domain controller to authenticate the login attempt coming from Excel. You can change your instance to support mixed mode authentication if it does not do that already and setup a SQL Server login for Excel to use which will take Active Directory out of the equation and should allow you to work offline.

Second issue: Chances are the reason why you cannot access the Database Diagrams while unplugged is related. Who is the owner of the database? You can check using this query just change the name to your DB name:

SELECT sp.name,

d.name

FROM sys.databases d

JOIN sys.server_principals sp ON d.owner_sid = sp.sid

WHERE d.name = 'database_name'

If the owner of your DB is a Windows domain account then it is likely that when you attempt to add the Database Diagram SQL Server is trying to make a call to Active Directory to check something about the database owner.

You can change the database owner to sa, unplug the network cable and try working with Database Diagrams to see if that solves the issue.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Gourou AWK Valued Member Points: 62 More actions · Answer 4

Many thanks for your clear explanation.

I'll try all your suggests soon.

I think I'll change Owner of database and login string to a local SQL Server user

Thanks again