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

Connecting via ODBC to SQLServer 2005 June CTP using MARS

Hello newsgroup,
after searching around the web, some reading and testing I'm still having
problems using MARS via ODBC with the SQLServer2005 June CTP.
I'm trying to enable MARS by connection string, after some looking about
"MARS_Connection=yes" should be the setting. That right?
I found different settings for OLEDB and ADO.NET I don't know why three
different strings are needed instead of one for all.
Anyway, it's not working and I'm getting the "connection is already full
with data from another result" message. I'm using the old odbc client for
SQLServer 2000, is that the problem?
I'd really like to use SQLServer in future, but I need MARS because else the
application would need some restructuring and that's too expensive. Currently
I'm working with two other DBMS and they work fine when it come to this
feature.
Thanks for help,
florian
Hi Florian,
Inorder for MARS to work you will need to use the new SQL Native Client; The
ODBC client for SQLServer 2000 does not support MARS.
ADO.NET, OLEDB and ODBC have different connection strings for MARS inorder
to preserve the naming conventions for connectino attributes bettween the
different providers.
Prior versions will get the 'connection busy' errors if you try to use
implicit multiple connections in and ODBC application.
Chris Lee has written an excellent blog about this here: It describes
migrating from MDAC to the SQL Native Client (very easy) and also about
using MARS
http://blogs.msdn.com/dataaccess/arc...gory/9952.aspx
There's a great technet article here as well
http://www.microsoft.com/technet/pro...marssql05.mspx
Brad Sarsfield [MSFT] bradsa(at)microsoft.com
This posting is provided "AS IS", with no warranties, and confers no rights.
"Florian B." <Florian B.@.discussions.microsoft.com> wrote in message
news:E4A95505-E345-45FF-A9AC-FFAD3E9E6ACF@.microsoft.com...
> Hello newsgroup,
> after searching around the web, some reading and testing I'm still having
> problems using MARS via ODBC with the SQLServer2005 June CTP.
> I'm trying to enable MARS by connection string, after some looking about
> "MARS_Connection=yes" should be the setting. That right?
> I found different settings for OLEDB and ADO.NET I don't know why three
> different strings are needed instead of one for all.
> Anyway, it's not working and I'm getting the "connection is already full
> with data from another result" message. I'm using the old odbc client for
> SQLServer 2000, is that the problem?
> I'd really like to use SQLServer in future, but I need MARS because else
> the
> application would need some restructuring and that's too expensive.
> Currently
> I'm working with two other DBMS and they work fine when it come to this
> feature.
> Thanks for help,
> florian

Connecting via ODBC to SQLServer 2005 June CTP using MARS

Hello newsgroup,
after searching around the web, some reading and testing I'm still having
problems using MARS via ODBC with the SQLServer2005 June CTP.
I'm trying to enable MARS by connection string, after some looking about
"MARS_Connection=yes" should be the setting. That right?
I found different settings for OLEDB and ADO.NET I don't know why three
different strings are needed instead of one for all.
Anyway, it's not working and I'm getting the "connection is already full
with data from another result" message. I'm using the old odbc client for
SQLServer 2000, is that the problem?
I'd really like to use SQLServer in future, but I need MARS because else the
application would need some restructuring and that's too expensive. Currentl
y
I'm working with two other DBMS and they work fine when it come to this
feature.
Thanks for help,
florianHi Florian,
Inorder for MARS to work you will need to use the new SQL Native Client; The
ODBC client for SQLServer 2000 does not support MARS.
ADO.NET, OLEDB and ODBC have different connection strings for MARS inorder
to preserve the naming conventions for connectino attributes bettween the
different providers.
Prior versions will get the 'connection busy' errors if you try to use
implicit multiple connections in and ODBC application.
Chris Lee has written an excellent blog about this here: It describes
migrating from MDAC to the SQL Native Client (very easy) and also about
using MARS
http://blogs.msdn.com/dataaccess/ar...egory/9952.aspx
There's a great technet article here as well
http://www.microsoft.com/technet/pr.../marssql05.mspx
Brad Sarsfield [MSFT] bradsa(at)microsoft.com
This posting is provided "AS IS", with no warranties, and confers no rights.
"Florian B." <Florian B.@.discussions.microsoft.com> wrote in message
news:E4A95505-E345-45FF-A9AC-FFAD3E9E6ACF@.microsoft.com...
> Hello newsgroup,
> after searching around the web, some reading and testing I'm still having
> problems using MARS via ODBC with the SQLServer2005 June CTP.
> I'm trying to enable MARS by connection string, after some looking about
> "MARS_Connection=yes" should be the setting. That right?
> I found different settings for OLEDB and ADO.NET I don't know why three
> different strings are needed instead of one for all.
> Anyway, it's not working and I'm getting the "connection is already full
> with data from another result" message. I'm using the old odbc client for
> SQLServer 2000, is that the problem?
> I'd really like to use SQLServer in future, but I need MARS because else
> the
> application would need some restructuring and that's too expensive.
> Currently
> I'm working with two other DBMS and they work fine when it come to this
> feature.
> Thanks for help,
> florian