How to make DB2 Connection String Dynamic, Password Problem

Hi All,

The problem I am facing is related to dynamic configuration of package one of the package connection is DB2 connection, I tried to set the expression connection string for that connection to the variable which contains the connection string to the DB2 but when I set connection the String property then i get the error message in transformation that password is missing, I dont want to write password in connection String for security reasons so I tried to save password in connection which is not helpful I am getting the same error message package security setting I changed to "Encrypt Sensitive Data with User Key" , anywayout to overcome this problem?

Thanks,

Manoj Kumar

Try setting ProtectionLevel to "SaveSensitiveWithPassword".

If you are using a configuration file to set the connection string, you can edit the .dtsconfig file directly to add the password into the connection string, but you should make sure the config file is stored in a secure location if you do this.

|||

I am using configuration setting and its based on (XML File and Table) XML Files points the configuration Database and Table stores the all configuration information so If I have to append the password configuration Table entry need to be changed but this is not required I am dealing with some sensitive data so they dont want the password of that db user stored somewhere exposed, some of the few questions related to this which I wanted to ask are as under.

1)If I Save password in Package then is there anywayout to bypass the password part from connection setting means package take password from the saved location not search in connection string. (user on different production databses is the same so mostly the dynamic part will be the Database only)

2)Is there anywayout to encrypt that password in configuration table entries.(Some users have access to the DB which holds the configuration table but they dont have access to production server)

if someone knows some other wayout to deal with this situation except the solution earlier provided.

Thanks and Regards

Manoj Kumar

sql

How to make a connection from 2000 to 2005?

We are using SQL Server Enterprise Manager to connect to all of our SQL 2000
servers - one server, one box. Now we have a new box running SQL Server
2005. Is there anyway we can make a connection from a SQL Server 2000 box to
SQL Server 2005 box? We need a way to 'see' the new server from the 2000
box. SQL Server 2000 box is running .NET Framework 1.1 and SQL Server 2005
box is running .NET Framework 2.0.
Thanks in advance.
-tcInstall the SQL Server 2005 Client Tools on the SQL Server 2000 server. This
will give you the SQL Server Management Studio, which can be used to connect
to SQL Server 2005.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"tcw" <tcwangs@.msn.com> wrote in message
news:%23w5rLqXtGHA.1216@.TK2MSFTNGP03.phx.gbl...
We are using SQL Server Enterprise Manager to connect to all of our SQL 2000
servers - one server, one box. Now we have a new box running SQL Server
2005. Is there anyway we can make a connection from a SQL Server 2000 box to
SQL Server 2005 box? We need a way to 'see' the new server from the 2000
box. SQL Server 2000 box is running .NET Framework 1.1 and SQL Server 2005
box is running .NET Framework 2.0.
Thanks in advance.
-tc|||You should be able to create a linked server between the boxes. You could
probably use openquery/openrowset to "see" the SQL 2005 box from the SQL
Server 2000 box.
As you have probably discovered, Enterprise Manager does not connect to SQL
Server 2005 boxes. You will need to use Query Analyzer or SQL Server
Management Studio (which is the client tools for SQL Server 2005). SSMS can
connect to a SQL Server 2000 install.
Keith Kratochvil
"tcw" <tcwangs@.msn.com> wrote in message
news:%23w5rLqXtGHA.1216@.TK2MSFTNGP03.phx.gbl...
> We are using SQL Server Enterprise Manager to connect to all of our SQL
> 2000 servers - one server, one box. Now we have a new box running SQL
> Server 2005. Is there anyway we can make a connection from a SQL Server
> 2000 box to SQL Server 2005 box? We need a way to 'see' the new server
> from the 2000 box. SQL Server 2000 box is running .NET Framework 1.1 and
> SQL Server 2005 box is running .NET Framework 2.0.
> Thanks in advance.
> -tc
>|||I installed SQL Server Management Studio and it works fine. Thanks a lot.
-tc
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:OQsFGHYtGHA.2020@.TK2MSFTNGP03.phx.gbl...
> You should be able to create a linked server between the boxes. You could
> probably use openquery/openrowset to "see" the SQL 2005 box from the SQL
> Server 2000 box.
> As you have probably discovered, Enterprise Manager does not connect to
> SQL Server 2005 boxes. You will need to use Query Analyzer or SQL Server
> Management Studio (which is the client tools for SQL Server 2005). SSMS
> can connect to a SQL Server 2000 install.
> --
> Keith Kratochvil
>
> "tcw" <tcwangs@.msn.com> wrote in message
> news:%23w5rLqXtGHA.1216@.TK2MSFTNGP03.phx.gbl...
>

How to make a connection from 2000 to 2005?

We are using SQL Server Enterprise Manager to connect to all of our SQL 2000
servers - one server, one box. Now we have a new box running SQL Server
2005. Is there anyway we can make a connection from a SQL Server 2000 box to
SQL Server 2005 box? We need a way to 'see' the new server from the 2000
box. SQL Server 2000 box is running .NET Framework 1.1 and SQL Server 2005
box is running .NET Framework 2.0.
Thanks in advance.
-tcInstall the SQL Server 2005 Client Tools on the SQL Server 2000 server. This
will give you the SQL Server Management Studio, which can be used to connect
to SQL Server 2005.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"tcw" <tcwangs@.msn.com> wrote in message
news:%23w5rLqXtGHA.1216@.TK2MSFTNGP03.phx.gbl...
We are using SQL Server Enterprise Manager to connect to all of our SQL 2000
servers - one server, one box. Now we have a new box running SQL Server
2005. Is there anyway we can make a connection from a SQL Server 2000 box to
SQL Server 2005 box? We need a way to 'see' the new server from the 2000
box. SQL Server 2000 box is running .NET Framework 1.1 and SQL Server 2005
box is running .NET Framework 2.0.
Thanks in advance.
-tc|||You should be able to create a linked server between the boxes. You could
probably use openquery/openrowset to "see" the SQL 2005 box from the SQL
Server 2000 box.
As you have probably discovered, Enterprise Manager does not connect to SQL
Server 2005 boxes. You will need to use Query Analyzer or SQL Server
Management Studio (which is the client tools for SQL Server 2005). SSMS can
connect to a SQL Server 2000 install.
--
Keith Kratochvil
"tcw" <tcwangs@.msn.com> wrote in message
news:%23w5rLqXtGHA.1216@.TK2MSFTNGP03.phx.gbl...
> We are using SQL Server Enterprise Manager to connect to all of our SQL
> 2000 servers - one server, one box. Now we have a new box running SQL
> Server 2005. Is there anyway we can make a connection from a SQL Server
> 2000 box to SQL Server 2005 box? We need a way to 'see' the new server
> from the 2000 box. SQL Server 2000 box is running .NET Framework 1.1 and
> SQL Server 2005 box is running .NET Framework 2.0.
> Thanks in advance.
> -tc
>|||I installed SQL Server Management Studio and it works fine. Thanks a lot.
-tc
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:OQsFGHYtGHA.2020@.TK2MSFTNGP03.phx.gbl...
> You should be able to create a linked server between the boxes. You could
> probably use openquery/openrowset to "see" the SQL 2005 box from the SQL
> Server 2000 box.
> As you have probably discovered, Enterprise Manager does not connect to
> SQL Server 2005 boxes. You will need to use Query Analyzer or SQL Server
> Management Studio (which is the client tools for SQL Server 2005). SSMS
> can connect to a SQL Server 2000 install.
> --
> Keith Kratochvil
>
> "tcw" <tcwangs@.msn.com> wrote in message
> news:%23w5rLqXtGHA.1216@.TK2MSFTNGP03.phx.gbl...
>> We are using SQL Server Enterprise Manager to connect to all of our SQL
>> 2000 servers - one server, one box. Now we have a new box running SQL
>> Server 2005. Is there anyway we can make a connection from a SQL Server
>> 2000 box to SQL Server 2005 box? We need a way to 'see' the new server
>> from the 2000 box. SQL Server 2000 box is running .NET Framework 1.1 and
>> SQL Server 2005 box is running .NET Framework 2.0.
>> Thanks in advance.
>> -tc
>sql

How to make a "SQL Server" connection with ASP.NET

Hi.

Working with ASP this connection works:

stringMyConn = "dsn=foo.com.bar;uid=john;pwd=xxxx;"
set myConn = Server.CreateObject("ADODB.Connection")
myConn.open stringMyConn

But it doesn't with ASP.NET

Dim myConn As SqlConnection = New SqlConnection("dsn=foo.com.bar;uid=john;pwd=xxxx;")
myConn.Open

What am I doing wrong? Thank you very much.

Since you are connecting to SQL Server, I don't recommend that you use a DSN connection. You can connect to SQL server as follows:

Dim myConn As New SqlConnection("server=;database=;user id=;password=;")
Dim myCmd As New SqlCommand("SELECT * FROM SOMETHING", myConn)

myConn.Open()

' ... Some code here to do something with the SqlCommand object

myConn.Close()

For future reference, a good source for connection strings isConnectionStrings.com.

How to look at "Set Up" options in somebody else's DB connctions?

Hello:
Is there a way that I can know what kind of DB Set up options are ON or
OFF in sombody else's connection? (Rather than asking him/her)
Options such as Concat_Null_Yields_Null, Ansi_Nulls, Ansi_Padding, etc.
Thank you.
CWIf you can run Profiler, then trace the 'existing connection' event and you
should see all the SET options for a given connection, uin the TextData
column.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Chai" <chai@.trs.state.il.us> wrote in message
news:uWvDh2U2DHA.1720@.TK2MSFTNGP10.phx.gbl...
Hello:
Is there a way that I can know what kind of DB Set up options are ON or
OFF in sombody else's connection? (Rather than asking him/her)
Options such as Concat_Null_Yields_Null, Ansi_Nulls, Ansi_Padding, etc.
Thank you.
CWsql

How to look at "Set Up" options in somebody else's DB connctions?

Hello:
Is there a way that I can know what kind of DB Set up options are ON or
OFF in sombody else's connection? (Rather than asking him/her) :)
Options such as Concat_Null_Yields_Null, Ansi_Nulls, Ansi_Padding, etc.
Thank you.
CWIf you can run Profiler, then trace the 'existing connection' event and you
should see all the SET options for a given connection, uin the TextData
column.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Chai" <chai@.trs.state.il.us> wrote in message
news:uWvDh2U2DHA.1720@.TK2MSFTNGP10.phx.gbl...
Hello:
Is there a way that I can know what kind of DB Set up options are ON or
OFF in sombody else's connection? (Rather than asking him/her) :)
Options such as Concat_Null_Yields_Null, Ansi_Nulls, Ansi_Padding, etc.
Thank you.
CW

How to log in to SQL with a user other than sa

my connection string is
"Data Source=w2k3-std;Initial Catalog=CTrack;User Id=sa;Password=********;"
I have created extra users in both my database and the master database but if I change the userid and password as above they fail to connect.
Why?
Cheers
PaulHi, the answer to your question is going to depend upon the errormessage you are seeing. Please be sure to post exact errormessages. This will help others to help you.
For new users, you need to assign them permission to log in to theserver, and you need to grant them permission to access your Ctrackdatabase. If you are having trouble doing this, let us know ifyou are using osql, Enterprise Manager, or some other tool.
And, BTW, you are making an excellent decision to stop using the saaccount for your application. This account should never be usedto access the database from any application.
|||

here is teh error i get when i try to login as a user called paul, i have created the user via enterprise manager both in the master database and my ctrack database. authentication is set to sql & windows (is this the best?) and what hav eI d one wrong or more likely not done at all.
Paul
Login failed for user 'paul'.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details:System.Data.SqlClient.SqlException: Login failed for user 'paul'.
Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException: Login failed for user 'paul'.] System.Data.SqlClient.ConnectionPool.CreateConnection() +402 System.Data.SqlClient.ConnectionPool.UserCreateRequest() +147 System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) +392 System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) +372 System.Data.SqlClient.SqlConnection.Open() +384 Web1.login.Button1_Click(Object sender, EventArgs e) +232 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain() +1292

|||Are you sure that the password for user paul is correct? Have you tried resetting it to make sure?
|||yep, I have reset it twice now just to be absolutely sure, where should I create the user though in the master db or my db?|||Well, you're going to actually *create* the user for that instance ofSQL Server, but you'll need to give it permissions to yourdatabase. I'd say that the easiest way to test accounts onceyou've initially created them, is to use them to log into QueryAnalyzer and try to run SQL statements directly. That makes iteasy to see what permissions they do and don't have, without beingbound to only the SQL statements that you application needs to run, andit also rules out potential connection string problems.
|||

i logged into the server as user paul and ran query analyser and quiereied (?) my database without any trouble, authentication is set to sql server and windows any more ideas anyone?

|||

I deleted the user and recreated it using sql authentication rather than windows authentication and now I can connect, what is the differenc and does anyone why I could not connect with windows auth?

Paul

|||Because the User ID and Password attributes of the connection string are specific to SQL authentication.
|||

I knew it had to be me doing something stupid....

thanks all

How to log in to SQL with a user other than sa

my connection string is
"Data Source=w2k3-std;Initial Catalog=CTrack;User Id=sa;Password=********;"
I have created extra users in both my database and the master database but if I change the userid and password as above they fail to connect.
Why?
Cheers
PaulHi, the answer to your question is going to depend upon the errormessage you are seeing. Please be sure to post exact errormessages. This will help others to help you.
For new users, you need to assign them permission to log in to theserver, and you need to grant them permission to access your Ctrackdatabase. If you are having trouble doing this, let us know ifyou are using osql, Enterprise Manager, or some other tool.
And, BTW, you are making an excellent decision to stop using the saaccount for your application. This account should never be usedto access the database from any application.
|||

here is teh error i get when i try to login as a user called paul, i have created the user via enterprise manager both in the master database and my ctrack database. authentication is set to sql & windows (is this the best?) and what hav eI d one wrong or more likely not done at all.
Paul
Login failed for user 'paul'.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details:System.Data.SqlClient.SqlException: Login failed for user 'paul'.
Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException: Login failed for user 'paul'.] System.Data.SqlClient.ConnectionPool.CreateConnection() +402 System.Data.SqlClient.ConnectionPool.UserCreateRequest() +147 System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) +392 System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) +372 System.Data.SqlClient.SqlConnection.Open() +384 Web1.login.Button1_Click(Object sender, EventArgs e) +232 System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain() +1292

|||Are you sure that the password for user paul is correct? Have you tried resetting it to make sure?
|||yep, I have reset it twice now just to be absolutely sure, where should I create the user though in the master db or my db?|||Well, you're going to actually *create* the user for that instance ofSQL Server, but you'll need to give it permissions to yourdatabase. I'd say that the easiest way to test accounts onceyou've initially created them, is to use them to log into QueryAnalyzer and try to run SQL statements directly. That makes iteasy to see what permissions they do and don't have, without beingbound to only the SQL statements that you application needs to run, andit also rules out potential connection string problems.
|||

i logged into the server as user paul and ran query analyser and quiereied (?) my database without any trouble, authentication is set to sql server and windows any more ideas anyone?

|||

I deleted the user and recreated it using sql authentication rather than windows authentication and now I can connect, what is the differenc and does anyone why I could not connect with windows auth?

Paul

|||Because the User ID and Password attributes of the connection string are specific to SQL authentication.
|||

I knew it had to be me doing something stupid....

thanks all

How to limit concurrent users when using pooled connections?

Hi,
I've been out of the loop for a while but when I learnt about db access from
VB etc we were told to use the same connection credentials such as username
and password in order to speed up access by recycling connections. Nowadays
I want to host a web site with some sort of database at the back end and I
would like to use MSDE but there is a 25 concurrent users restraint on the
license.
How would I limit the users if I'm using connection pooling? Is this
something I'd have to do in code?
Thanks in advance.
hi,
meadensi wrote:
> Hi,
> I've been out of the loop for a while but when I learnt about db
> access from VB etc we were told to use the same connection
> credentials such as username and password in order to speed up access
> by recycling connections. Nowadays I want to host a web site with
> some sort of database at the back end and I would like to use MSDE
> but there is a 25 concurrent users restraint on the license.
there is no restrincions on licences at all..this 25 magic number is just a
general "guess" about the "potential" limit of MSDE, as it allows up to
32765 theoretical concurrent connections (as all SQL Server editions) but it
has a built in Query Governor ( more at
http://msdn.microsoft.com/library/?u...asp?frame=true )
that kicks in when 8 concurrent workloads (AKA batches and not connections)
are executing, slowing down all active workloads...

> How would I limit the users if I'm using connection pooling? Is this
> something I'd have to do in code?
as you are writing a web base application, probably built on IIS, you can
for sure use the connection pooling features, as the application server
probably uses the very same connection string each time (and this is enougth
for reusing pooled connections), but disabling the pooled connections
(providing the "OLE DB Services = -2" parameter in the connection string,
which disables connection pooling only...) could not increase your
performance/access strategy, as the wall still is at 8 concurrent workloads,
with no regard to the actual connection owner... and querying your
master..sysprocesses table for the actual current count only adds additional
workloads to your (scarce) resources...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thanks Andrea. Very good and helpful answer which has reminded me of why the
MVP programme is a good idea (I once pondered trying to earn MVP status
myself, in Excel!)
Cheers,
meadensi
"Andrea Montanari" wrote:

> hi,
> meadensi wrote:
> there is no restrincions on licences at all..this 25 magic number is just a
> general "guess" about the "potential" limit of MSDE, as it allows up to
> 32765 theoretical concurrent connections (as all SQL Server editions) but it
> has a built in Query Governor ( more at
> http://msdn.microsoft.com/library/?u...asp?frame=true )
> that kicks in when 8 concurrent workloads (AKA batches and not connections)
> are executing, slowing down all active workloads...
>
> as you are writing a web base application, probably built on IIS, you can
> for sure use the connection pooling features, as the application server
> probably uses the very same connection string each time (and this is enougth
> for reusing pooled connections), but disabling the pooled connections
> (providing the "OLE DB Services = -2" parameter in the connection string,
> which disables connection pooling only...) could not increase your
> performance/access strategy, as the wall still is at 8 concurrent workloads,
> with no regard to the actual connection owner... and querying your
> master..sysprocesses table for the actual current count only adds additional
> workloads to your (scarce) resources...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
>