2

I have a working SQL Server 2008 R2 Express installed on a Windows 2008 server on a cloud machine. All type of connections (ado.net, nhibernate etc.) from several client applications works fine except the OLEDB one. SQL Native Client also works fine.

When I tried to connect from the server locally and as a data source write the instance name, everything was OK. But from outside, I had the problem or also if I write wan ip or dns I had the problem also from local.

-- works perfect from everywhere
Provider=SQLNCLI10;Data Source=mntek.no-ip.org;User ID=xxx;Password=xxx; 

-- does not work from anywhere
Provider=SQLOLEDB.1;Data Source=mntek.no-ip.org;User ID=xxx;Password=xxx;

-- works only from local
Provider=SQLOLEDB.1;Data Source=flz001\sqlexpress;User ID=xxx;Password=xxx;

Error: [DBNETLIB][ConnectionOpen (Invalid Instance()).Invalid connection.

-- works perfect with ado.net
Data Source=mntek.no-ip.org;User ID=xxx;Password=xxx

Some other client applications (not mine) was written with oledb connection so I need them to connect the db with oledb provider.

Any ideas?

Regards, Mert

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
mece
  • 21
  • 1
  • 2
  • Did you try using IP instead of name? Also try adding `;Network=DBMSSOCN` to the connection string? I'm also confused - is this a named instance (`\sqlexpress`) or a default instance? Your connection strings are contradicting each other. – Aaron Bertrand Feb 21 '12 at 14:12
  • Hi Aaron, I also tried IP and adding Network property but still does not work. In the Properties - Name writes FLZ001\SQLEXPRESS in management studio so I guess its a named instance (installed by customer). I tried every possibility for connection string so they may be contradict each other. – mece Mar 12 '12 at 15:26

1 Answers1

6

It has been a while since this was asked, but I am currently working through an issue related to OLE DB and SQL Server that sounds possibly related.

I found that, even though the server was using the default port (1433), I still had to explicitly specify it in the UDL connection string like this:

Provider=SQLOLEDB.1;User ID=USER;Data Source=IP,1433

I've asked why that might be necessary here: Why might SQL Server 2008 OLE DB UDL require port 1433 explicitly specified?

Maybe try that and see if it helps.

Community
  • 1
  • 1
incandenza
  • 138
  • 1
  • 2
  • 6