Connection manager permission issue

Hi there,

I apologize upfront if this is an old issue but i couldn't find the solution after searching quite a bit.

I have a OLE db connection manager and an ADO.net db connection manager and both of them fail when i run them in VS dev environment:

Connection may not be configured correctly or you may not have the right permissions on this connection

When I set up my connection managers, they do connect fine but no matter which one i try to use with an Execute sql task, the above error keeps popping up.

The connection managers are both set up to use SQL Authentication and the database is not on my local machine but instead on a server and my package security is set to 'DontSaveSensitive'. I have not yet deployed so this is all in my VS.net dev environment where it's failing.

I have also turned off the firewall on both the development server and my pc. All I want to be able to do is let the connection managers point the the dev server as the db is on there.

Any suggestions?

Regards
Mike
Hi,

I found a solution for the problem and thought it might be useful to post it for those that seek to develop using a remote dev server that contains the database.

I declared a variable that contained the connectionstring for the specific connection manager (note that the ado.Net connection string will look slightly different than oledb for instance) and in the connection manager's expression property pointed it to the variable.

I got pointed into this direction by reading the article 'Suggested best practices' by Jamie Thomson :
http://blogs.conchango.com/jamiethomson/archive/2006/01/05/2554.aspx

Thanks for that excellent article Jamie
Regards
Michael

Connection for the ADO RecordSet

Hi! All,

I have an old ado application like following:

pCadoCon = new CADOConnection;

pCadoCon->Open (_T"driver={...A OEM driver...}; UID=..;PWD=....", _T (""), _T (""));

//some OEM driver unlocking code

pCadors = new CADORs;

pCadors->Open (_T"select foo from bar", pCadoCon->m_lpDispatch, eCursor); //SQL: a select statement here

The CADOConnection and CADORs are ADO connection, RecordSet wrapper classes created with ClassWizard.

I successfully open an ADO connection using the OEM driver by this DSN less way, and then passed it to ADO Recordset. From ODBC trace, I found that under the cover of the ADO, this connection is used to execute the select statement, but after that it automatically opens an other connection. I do not know why and is it possible to disable this?

I suspect it's opening another connection to provide a back channel to post updates to a scrollable, updatable rowset. Can it be disabled? Ah, sure. Open a ROFO Recordset.

connection error

Hello,
We just add a new SQL2000 server (SQL-3, 192.168.100.3) w/SP4 installed.
We have old SQL-1(192.168.100.1) and SQL-2(192.168.100.2) servers.
All 3 servers are in same location.
SQL-1 and SQL-2 are in same domain, i.e. domain-a.
SQL-3 is another domain, i.e. domain-b.
SQL-3 can add SQL-1 and SQL-2 from 'New SQL Server Registration'.
(We did not add SQL-1 and SQL-2 on 'Client Network Utility')
But SQL-1 or SQL-2 can not add SQL-3 from 'New SQL Server Registration'.
We use 'Client Network Utility' add alias with TCP/IP 192.168.100.3 and port
1433.
Then we have the message 'SQL Server does not exist or access denied
connection open (Connect[])' during add SQL-3 from 'New SQL Server
Registration'.
SQL-3's 'Allow other SQL Server to connect remotelly to this SQL Server
using RPC' is checked.
Thanks
Hi
This could be a firewall issue or you may be using dynamic ports (if this is
a named instance for example). Have you checked which port SQL3 is listening
on? Can you telnet on the given port to SQL3 from the other servers? Have you
tried using IP addresses instead of DNS Names?
You may want to check out SQLPing at
http://www.sqlsecurity.com/Tools/FreeTools/tabid/65/Default.aspx
Also check the client protocols being used your service pack would indicate
TCP/IP should be first.
John
"Newbie" wrote:

> Hello,
> We just add a new SQL2000 server (SQL-3, 192.168.100.3) w/SP4 installed.
> We have old SQL-1(192.168.100.1) and SQL-2(192.168.100.2) servers.
> All 3 servers are in same location.
> SQL-1 and SQL-2 are in same domain, i.e. domain-a.
> SQL-3 is another domain, i.e. domain-b.
> SQL-3 can add SQL-1 and SQL-2 from 'New SQL Server Registration'.
> (We did not add SQL-1 and SQL-2 on 'Client Network Utility')
> But SQL-1 or SQL-2 can not add SQL-3 from 'New SQL Server Registration'.
> We use 'Client Network Utility' add alias with TCP/IP 192.168.100.3 and port
> 1433.
> Then we have the message 'SQL Server does not exist or access denied
> connection open (Connect[])' during add SQL-3 from 'New SQL Server
> Registration'.
> SQL-3's 'Allow other SQL Server to connect remotelly to this SQL Server
> using RPC' is checked.
> Thanks

connection error

Hello,
We just add a new SQL2000 server (SQL-3, 192.168.100.3) w/SP4 installed.
We have old SQL-1(192.168.100.1) and SQL-2(192.168.100.2) servers.
All 3 servers are in same location.
SQL-1 and SQL-2 are in same domain, i.e. domain-a.
SQL-3 is another domain, i.e. domain-b.
SQL-3 can add SQL-1 and SQL-2 from 'New SQL Server Registration'.
(We did not add SQL-1 and SQL-2 on 'Client Network Utility')
But SQL-1 or SQL-2 can not add SQL-3 from 'New SQL Server Registration'.
We use 'Client Network Utility' add alias with TCP/IP 192.168.100.3 and port
1433.
Then we have the message 'SQL Server does not exist or access denied
connection open (Connect[])' during add SQL-3 from 'New SQL Server
Registration'.
SQL-3's 'Allow other SQL Server to connect remotelly to this SQL Server
using RPC' is checked.
ThanksHi
This could be a firewall issue or you may be using dynamic ports (if this is
a named instance for example). Have you checked which port SQL3 is listening
on? Can you telnet on the given port to SQL3 from the other servers? Have yo
u
tried using IP addresses instead of DNS Names?
You may want to check out SQLPing at
http://www.sqlsecurity.com/Tools/Fr...65/Default.aspx
Also check the client protocols being used your service pack would indicate
TCP/IP should be first.
John
"Newbie" wrote:

> Hello,
> We just add a new SQL2000 server (SQL-3, 192.168.100.3) w/SP4 installed.
> We have old SQL-1(192.168.100.1) and SQL-2(192.168.100.2) servers.
> All 3 servers are in same location.
> SQL-1 and SQL-2 are in same domain, i.e. domain-a.
> SQL-3 is another domain, i.e. domain-b.
> SQL-3 can add SQL-1 and SQL-2 from 'New SQL Server Registration'.
> (We did not add SQL-1 and SQL-2 on 'Client Network Utility')
> But SQL-1 or SQL-2 can not add SQL-3 from 'New SQL Server Registration'.
> We use 'Client Network Utility' add alias with TCP/IP 192.168.100.3 and po
rt
> 1433.
> Then we have the message 'SQL Server does not exist or access denied
> connection open (Connect[])' during add SQL-3 from 'New SQL Server
> Registration'.
> SQL-3's 'Allow other SQL Server to connect remotelly to this SQL Server
> using RPC' is checked.
> Thanks