Configure "Remote Connections" via Group Policy?

Is it possible to create a group policy that sets the "remote connections"
property that exists in the SQL Server Surface Area Configuration applet?
How? :-)
Thanks,
JoeNo. This is also something you do NOT want to do. If you could create
this, it would mean that every single SQL Server instance, including the
various SQL Server Express instances installed with thousands of different
applications would automatically be able to receive inbound connections from
remote clients. This was a very specific security issue that was addressed
in this version. All you have to do is to look up the Slammer worm and
you'll understand why you only want those instances which need to service
client connections able to do so.
Mike
http://www.solidqualitylearning.com
Disclaimer: This communication is an original work and represents my sole
views on the subject. It does not represent the views of any other person
or entity either by inference or direct reference.
"Joe" <jwdaigle@.nospam.nospam> wrote in message
news:Ow0cVf8KGHA.3396@.TK2MSFTNGP10.phx.gbl...
> Is it possible to create a group policy that sets the "remote connections"
> property that exists in the SQL Server Surface Area Configuration applet?
> How? :-)
> Thanks,
> Joe
>|||And if I wanted to ensure that it was never set to allow remote connections?
"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:uGopFv$KGHA.3264@.TK2MSFTNGP11.phx.gbl...
> No. This is also something you do NOT want to do. If you could create
> this, it would mean that every single SQL Server instance, including the
> various SQL Server Express instances installed with thousands of different
> applications would automatically be able to receive inbound connections
> from remote clients. This was a very specific security issue that was
> addressed in this version. All you have to do is to look up the Slammer
> worm and you'll understand why you only want those instances which need to
> service client connections able to do so.
> --
> Mike
> http://www.solidqualitylearning.com
> Disclaimer: This communication is an original work and represents my sole
> views on the subject. It does not represent the views of any other person
> or entity either by inference or direct reference.
>
> "Joe" <jwdaigle@.nospam.nospam> wrote in message
> news:Ow0cVf8KGHA.3396@.TK2MSFTNGP10.phx.gbl...
>
>|||Hi Joe,
Welcome to use MSDN Managed Newsgroup Support. And thank Michael's great
help!
As Michael stated, it does not mean that never set to allow remote
connection. You can allow remote connection for those instance you want to.
By default, only Enterprise, Standard and Workgroup editions allow remote
client connection.
If you have any questions or concerns, don't hesitate to let me know. We
are here to be of assistance!
Sincerely yours,
Wei Lu
Microsoft Online Partner Support
========================================
==============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||So then it is not possible to ensure that "allow remote client connections"
is disabled via group policy? Thats too bad, because it requires me to
manually check systems for compliance.
Ok, thank you both for your help.
Joe
"Wei Lu" <t-weilu@.online.microsoft.com> wrote in message
news:Ap4y3JHLGHA.3052@.TK2MSFTNGXA01.phx.gbl...
> Hi Joe,
> Welcome to use MSDN Managed Newsgroup Support. And thank Michael's great
> help!
> As Michael stated, it does not mean that never set to allow remote
> connection. You can allow remote connection for those instance you want
> to.
> By default, only Enterprise, Standard and Workgroup editions allow remote
> client connection.
> If you have any questions or concerns, don't hesitate to let me know. We
> are here to be of assistance!
> Sincerely yours,
> Wei Lu
> Microsoft Online Partner Support
> ========================================
==============
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
>|||You are welcome, Joe.
Sincerely yours,
Wei Lu
Microsoft Online Partner Support
========================================
==============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

Configuration file reused by different packages

I have two packages share same configuration file. There are 4 connections in the first package and 2 connections in the second. Configuration file contains properties for 4 connections. First package opens in VS designer with no error. The problem with second package.

I get error that connection exist in the configuration but not in the package

and

"Could not copy file " Master_Configuration.dtsConfig" to the deployment utility output directory. Master_Configuration.dtsConfig' already exists."

Any help?

For the former error, I'd suggest just using one config per connection. It may seem annoying but over a large project it works quite well and offers flexibility. If you do have common connections in all packages, then you can group them, but even that is not necessary.

Not really used the deployment tool, so cannot comment on the second error.

|||

It is more convenient to use one (master) configuration file. I use dtexec utility to run packages in production. With many config files I would have to dynamically bild /ConfigFile parameter for each file for each package.

|||I would set the configurations at design-time. I think this is a much better option all round. It is easy to change development systems, and ensures you can use the ProtectionLevel DontSaveSensitive option even if you require passwords for connections without any problems. Using indirect configurations means you can handle changing paths for your config files as you move through environments.

Configuration Approach

Background

I use four database connections strings.

I have about 30 packages that will use one or more of the connection stings.

I store the connection strings in XML configuration files.

I know that I can share configuration files across packages. Should I ?

Have one configuration file with all 4 connection strings? If I use this approach will I get errors in the packages that only use 1 of the connection strings OR

Create four separate configuration files (CnnString1.dtsconfig, CnnString2.dtsconfig, CnnString3.dtsconfig, CnnString4.dtsconfig) and use the appropriate ones for each package

Take another approach

Check this thread, It discusses the similar kind of issue.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1853477&SiteID=1

Thanks

|||For me, I just create all of the connection managers that are in the config file across all packages for a given project. It's a pain to setup, but it works for me.|||

That is the pain I am striving to avoid by using a modular approach. I know that having a configuration file for each package works but it is not reusable. That can cause a maintenance nightmare when moving 30+ packages between Dev, QA & Prod environments.

I have a requirement to use XML Configuration files. Returning to the original question, should I use 1 file with all database connections or should I use 4 files with a connection in each?

|||

Danny Crowell wrote:

That is the pain I am striving to avoid by using a modular approach. I know that having a configuration file for each package works but it is not reusable. That can cause a maintenance nightmare when moving 30+ packages between Dev, QA & Prod environments.

I have a requirement to use XML Configuration files. Returning to the original question, should I use 1 file with all database connections or should I use 4 files with a connection in each?

If it's such a pain, then use a configuration file for each connection manager object. Then each package only uses what it needs.

Configuration

Hi,

In my package I am using some 3 to 4 database connections. Hence I can say 3 to 4 connection managers I have. Other than that I have 2 Flat file connection manager.

What will be the best way to configure the same? and Please let me knowwhy that is the best choice.

Thanks

There is no "best" way. It depends on your environment and what works for you.

Personally I use XML configuration files. And I like to have a seperate configuration file for each connection manager. And I like to use indirect configurations.

but that's just me!

-Jamie

Conection

Hello!!
Please help me!!
I want to know, if SQL server has a table where I can
find how many connections ar in this moment, or a
command. Or beteer,I what to know how many computers ar
login to SQL server ( USER ID or Network card ID) in this
moment.
Thank you,
Ionut
Hi,
execute the below procedure from query analyzer:-
sp_who
or
use the system table to query the user connections, host name, network
address etc..:-
select * from master..sysprocesses
Thanks
Hari
MCDBA
"Ionut Terhes" <tjonut@.yahoo.com> wrote in message
news:c94a01c438f1$c6f56020$a001280a@.phx.gbl...
> Hello!!
> Please help me!!
> I want to know, if SQL server has a table where I can
> find how many connections ar in this moment, or a
> command. Or beteer,I what to know how many computers ar
> login to SQL server ( USER ID or Network card ID) in this
> moment.
> Thank you,
> Ionut
|||You can also use sp_who2 which has additional information than sp_who.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Conection

Hello!!
Please help me!!
I want to know, if SQL server has a table where I can
find how many connections ar in this moment, or a
command. Or beteer,I what to know how many computers ar
login to SQL server ( USER ID or Network card ID) in this
moment.
Thank you,
IonutHi,
execute the below procedure from query analyzer:-
sp_who
or
use the system table to query the user connections, host name, network
address etc..:-
select * from master..sysprocesses
Thanks
Hari
MCDBA
"Ionut Terhes" <tjonut@.yahoo.com> wrote in message
news:c94a01c438f1$c6f56020$a001280a@.phx.gbl...
> Hello!!
> Please help me!!
> I want to know, if SQL server has a table where I can
> find how many connections ar in this moment, or a
> command. Or beteer,I what to know how many computers ar
> login to SQL server ( USER ID or Network card ID) in this
> moment.
> Thank you,
> Ionut|||You can also use sp_who2 which has additional information than sp_who.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Conditional checks in SSIS.

I have two connections in a package pointing to two different databases
on the same server. I have to insert records from 'DB1' table 'Gender1'
to 'DB2' table 'Gender2'. Before I do that though, I have to make sure
the minimum value (of all the Gender Keys that are going to be
inserted) of 'DB1' 'GenderKey' (which is an identity field) is greater than the
maximum value of DB2-GenderKey (which is a primary key but not an
identity field). How can I do this simple check? I have to do this process for many different tables ....... Gender table is just an example. If someone can give an detailed explanation on which tasks to use and how to use them (as I am relatively new to SSIS) that'd be great.So you want to move the rows from DB1 that are newer than what you have in DB2.

You should use an Execute Task in the Control Flow to get the ISNULL(MAX(GenderKey) ,0) from DB2 and place that in a variable. Have another variable with EvaluateAsExpression=True that builds a SQL statement with your MaxGenderKey variable to get any rows where GenderKey in DB1 is greater than the MaxGenderKey from DB2. Set the data access mode of your OLE DB Source to "SQL command from variable" and specify this expression-based variable.
|||actually, i do not want to do insert at all. If the condition check is successfull, then do the insert otherwise abort the package? How do i do that?|||You could certainly do that. You'd get the MaxGenderKey from DB1 as well and compare them in a precedence constraint to prevent any further execution.

Unless you're changing your original purpose of inserting new records from DB1 to DB2, it seems simpler to just let it try. If there aren't any, then it won't find anything to do.
|||

Use one Execute SQL to get the Max value from DB2, and one to get the Max value from DB1. Store the values in variables, as Jay described. Then have a data flow to do the actual inserts. Have a precendence constraint between the 2 Execute SQL tasks, and one between the 2nd Execute SQL and the data flow. Right-click on the precedence constraint between the 2nd Execute SQL and the data flow task, and change the Evaluation Operation to Expression and Constraint. Leave the Value as Success, and change the Expression to compare the variables you created (something like MaxIDFromDB1 > MaxIDFromDB2). Execution will only flow to the data flow if the expression evaluates to TRUE.

Exec SQL > Exec SQL --(expression goes here)-> Data Flow

|||

I think I might be getting what you guys are trying to convey.

So here is what I did.

I added a Sequence Container.

1. I added a Execute Sql Task to it which brings the GenderKey from Prod and puts it in a Variable using the following query ..

SELECT [User::GenderKey_Prod] = ISNULL(MAX(GenderKey) ,0) FROM dbo.DIM_Gender

2. I added another Execute Sql Task which brings the GenderKey from Staging and puts it in another Variable using following query ..

SELECT [User::GenderKey_Stg] = MIN(dg.GenderKey) FROM medical.Fact_Claim mfc
INNER JOIN dbo.Dim_Patient dp ON
dp.PatientKey = mfc.PatientKey
INNER JOIN dbo.Dim_Gender dg ON
dp.GenderKey = dg.GenderKey
WHERE mfc.TaskID = ?

I added the connector from Prod to Stg (1 -> 2).

3. Then added another data flow task (which will actually transfer the data).

4. And added a connector from STG to this data (2 -> 3) flow task and on this connector I defined the expression check @.

[User::GenderKey_Prod] < @. [User::GenderKey_Stg] which if true, will execute the data flow task.

All this makes sense, however, I am having troubles on step 2. Where condition task a parameter called TaskId, this parameter is a

Package Level parameter I have added. I did the parameter mapping, but it keeps giving error on this step says 'Parameter Name is

Unrecognized'. Can you see anything wrong that I have done in Step 2 while defining runtime parameters?

|||

Ok, I think I found the issue. The Parameter name in mapping section has to be 0.

But I still have an issue, now the package is running and executing step 1 and 2, after than its just not executing step 3 even if the expression is true. How can i check the values of the variables I assigned value to in step 1 and 2 ?

|||

ASOOD wrote:

SELECT [User::GenderKey_Stg] = MIN(dg.GenderKey) FROM medical.Fact_Claim mfc
INNER JOIN dbo.Dim_Patient dp ON
dp.PatientKey = mfc.PatientKey
INNER JOIN dbo.Dim_Gender dg ON
dp.GenderKey = dg.GenderKey
WHERE mfc.TaskID = ?

On the Parameter Mapping page make sure you specify 0 for the Parameter Name to represent the first parameter.

I'm not too sure about your SELECT syntax. You know you have to set the ResultSet to Single Row and make an entry on the Result Set page? You'll specify the output column name (which in your query above is [User::GenderKey_Stg]) and select the variable.

You also need to cast the MIN and MAX to avoid and error from SSIS. Those will come back as an object datatype unless you do it like CAST(MIN(dg.GenderKey) AS int).
|||Right-click on step 3 and add a breakpoint to the OnPreexecute step. Then run (debug) the package. When processing stops, you can go to the Debug menu and show the locals window. From there, drill down until you find your variables. Hit the run button to continue.|||

JayH wrote:

You also need to cast the MIN and MAX to avoid and error from SSIS. Those will come back as an object datatype unless you do it like CAST(MIN(dg.GenderKey) AS int).

I have not had this problem... Can you post a repro?|||I was able to debug the values, the prod variables has a value of 0 (it should be 2), I ran the query in Enterprise Manager and it returns 2 so obviously the way i am assigning value in the variable is wrong. I tried casting it, it still always shows 0 ?|||

Phil Brammer wrote:

JayH wrote:

You also need to cast the MIN and MAX to avoid and error from SSIS. Those will come back as an object datatype unless you do it like CAST(MIN(dg.GenderKey) AS int).

I have not had this problem... Can you post a repro?

Sorry, I should have qualified the statement. It will only happen if your datatype in SQL is not int. I was testing with a table that had a smallint and thus had to supply a CAST.
|||

ASOOD wrote:

I was able to debug the values, the prod variables has a value of 0 (it should be 2), I ran the query in Enterprise Manager and it returns 2 so obviously the way i am assigning value in the variable is wrong. I tried casting it, it still always shows 0 ?

Did you set the ResultSet to "Single Row" and create a mapping between the result column and the variable?
|||You guys are the best!! Works like a charm now. Thank you all.|||Thanks for the example as I was trying to do the same thing for the first time.

Conditional checks in SSIS.

I have two connections in a package pointing to two different databases
on the same server. I have to insert records from 'DB1' table 'Gender1'
to 'DB2' table 'Gender2'. Before I do that though, I have to make sure
the minimum value (of all the Gender Keys that are going to be
inserted) of 'DB1' 'GenderKey' (which is an identity field) is greater than the
maximum value of DB2-GenderKey (which is a primary key but not an
identity field). How can I do this simple check? I have to do this process for many different tables ....... Gender table is just an example. If someone can give an detailed explanation on which tasks to use and how to use them (as I am relatively new to SSIS) that'd be great.So you want to move the rows from DB1 that are newer than what you have in DB2.

You should use an Execute Task in the Control Flow to get the ISNULL(MAX(GenderKey) ,0) from DB2 and place that in a variable. Have another variable with EvaluateAsExpression=True that builds a SQL statement with your MaxGenderKey variable to get any rows where GenderKey in DB1 is greater than the MaxGenderKey from DB2. Set the data access mode of your OLE DB Source to "SQL command from variable" and specify this expression-based variable.
|||actually, i do not want to do insert at all. If the condition check is successfull, then do the insert otherwise abort the package? How do i do that?|||You could certainly do that. You'd get the MaxGenderKey from DB1 as well and compare them in a precedence constraint to prevent any further execution.

Unless you're changing your original purpose of inserting new records from DB1 to DB2, it seems simpler to just let it try. If there aren't any, then it won't find anything to do.
|||

Use one Execute SQL to get the Max value from DB2, and one to get the Max value from DB1. Store the values in variables, as Jay described. Then have a data flow to do the actual inserts. Have a precendence constraint between the 2 Execute SQL tasks, and one between the 2nd Execute SQL and the data flow. Right-click on the precedence constraint between the 2nd Execute SQL and the data flow task, and change the Evaluation Operation to Expression and Constraint. Leave the Value as Success, and change the Expression to compare the variables you created (something like MaxIDFromDB1 > MaxIDFromDB2). Execution will only flow to the data flow if the expression evaluates to TRUE.

Exec SQL > Exec SQL --(expression goes here)-> Data Flow

|||

I think I might be getting what you guys are trying to convey.

So here is what I did.

I added a Sequence Container.

1. I added a Execute Sql Task to it which brings the GenderKey from Prod and puts it in a Variable using the following query ..

SELECT [User::GenderKey_Prod] = ISNULL(MAX(GenderKey) ,0) FROM dbo.DIM_Gender

2. I added another Execute Sql Task which brings the GenderKey from Staging and puts it in another Variable using following query ..

SELECT [User::GenderKey_Stg] = MIN(dg.GenderKey) FROM medical.Fact_Claim mfc
INNER JOIN dbo.Dim_Patient dp ON
dp.PatientKey = mfc.PatientKey
INNER JOIN dbo.Dim_Gender dg ON
dp.GenderKey = dg.GenderKey
WHERE mfc.TaskID = ?

I added the connector from Prod to Stg (1 -> 2).

3. Then added another data flow task (which will actually transfer the data).

4. And added a connector from STG to this data (2 -> 3) flow task and on this connector I defined the expression check @.

[User::GenderKey_Prod] < @. [User::GenderKey_Stg] which if true, will execute the data flow task.

All this makes sense, however, I am having troubles on step 2. Where condition task a parameter called TaskId, this parameter is a

Package Level parameter I have added. I did the parameter mapping, but it keeps giving error on this step says 'Parameter Name is

Unrecognized'. Can you see anything wrong that I have done in Step 2 while defining runtime parameters?

|||

Ok, I think I found the issue. The Parameter name in mapping section has to be 0.

But I still have an issue, now the package is running and executing step 1 and 2, after than its just not executing step 3 even if the expression is true. How can i check the values of the variables I assigned value to in step 1 and 2 ?

|||

ASOOD wrote:

SELECT [User::GenderKey_Stg] = MIN(dg.GenderKey) FROM medical.Fact_Claim mfc
INNER JOIN dbo.Dim_Patient dp ON
dp.PatientKey = mfc.PatientKey
INNER JOIN dbo.Dim_Gender dg ON
dp.GenderKey = dg.GenderKey
WHERE mfc.TaskID = ?

On the Parameter Mapping page make sure you specify 0 for the Parameter Name to represent the first parameter.

I'm not too sure about your SELECT syntax. You know you have to set the ResultSet to Single Row and make an entry on the Result Set page? You'll specify the output column name (which in your query above is [User::GenderKey_Stg]) and select the variable.

You also need to cast the MIN and MAX to avoid and error from SSIS. Those will come back as an object datatype unless you do it like CAST(MIN(dg.GenderKey) AS int).
|||Right-click on step 3 and add a breakpoint to the OnPreexecute step. Then run (debug) the package. When processing stops, you can go to the Debug menu and show the locals window. From there, drill down until you find your variables. Hit the run button to continue.|||

JayH wrote:

You also need to cast the MIN and MAX to avoid and error from SSIS. Those will come back as an object datatype unless you do it like CAST(MIN(dg.GenderKey) AS int).

I have not had this problem... Can you post a repro?|||I was able to debug the values, the prod variables has a value of 0 (it should be 2), I ran the query in Enterprise Manager and it returns 2 so obviously the way i am assigning value in the variable is wrong. I tried casting it, it still always shows 0 ?|||

Phil Brammer wrote:

JayH wrote:

You also need to cast the MIN and MAX to avoid and error from SSIS. Those will come back as an object datatype unless you do it like CAST(MIN(dg.GenderKey) AS int).

I have not had this problem... Can you post a repro?

Sorry, I should have qualified the statement. It will only happen if your datatype in SQL is not int. I was testing with a table that had a smallint and thus had to supply a CAST.
|||

ASOOD wrote:

I was able to debug the values, the prod variables has a value of 0 (it should be 2), I ran the query in Enterprise Manager and it returns 2 so obviously the way i am assigning value in the variable is wrong. I tried casting it, it still always shows 0 ?

Did you set the ResultSet to "Single Row" and create a mapping between the result column and the variable?
|||You guys are the best!! Works like a charm now. Thank you all.|||Thanks for the example as I was trying to do the same thing for the first time.

Concurrent connections/sessions for SQL 2000

How many concurrent sessions can SQL 2000 take?
thanks
"Jeff" <anonymous@.discussions.microsoft.com> wrote in message
news:c2a701c489f7$b69bd590$a501280a@.phx.gbl...
> How many concurrent sessions can SQL 2000 take?
Theoretically maximum is 32,767 user connections. Actual number will vary
based on edition of SQL Server 2000 installed and hardware in use.
Steve

Concurrent connections/sessions for SQL 2000

How many concurrent sessions can SQL 2000 take?
thanks"Jeff" <anonymous@.discussions.microsoft.com> wrote in message
news:c2a701c489f7$b69bd590$a501280a@.phx.gbl...
> How many concurrent sessions can SQL 2000 take?
Theoretically maximum is 32,767 user connections. Actual number will vary
based on edition of SQL Server 2000 installed and hardware in use.
Steve|||Steve,
how about for SQL 2000 Enterprise?
the reason I am asking is because I had an issue where we
were getting asp errors/access denied during heavy traffic
to our web servers/db servers.
Our web server and db server are on 2 sep. machines.
Pbviously, the web server connects to the db server.
The web server is on wind.2003.
anyways, that specific day, we got heavy traffic and some
of the connections were timing out. I looked at the SQL
processes in Enterprise Manager and saw that lots of
proccesses were sleeping, so i am not sure what to make of
this. I looked at perfmon and saw that the hardware was up
to spec with no issues...
any ideas? is this a app/coding issue?
thanks

>--Original Message--
>"Jeff" <anonymous@.discussions.microsoft.com> wrote in
message
>news:c2a701c489f7$b69bd590$a501280a@.phx.gbl...
>Theoretically maximum is 32,767 user connections. Actual
number will vary
>based on edition of SQL Server 2000 installed and
hardware in use.
>Steve
>
>.
>

Concurrent Connections

HI All
Could help me out to find the following information from the Query
Analyzer?
Number of Concurrent Connections/Users
Average Transaction Per/Sec
Database Growth Rate
RegardsHi,
You can querry system table master..sysprocesses and get required counters.
Amol Lembhe
"Praveen" wrote:

> HI All
> Could help me out to find the following information from the Query
> Analyzer?
> Number of Concurrent Connections/Users
> Average Transaction Per/Sec
> Database Growth Rate
> Regards
>

Concurrent Connections

HI All
Could help me out to find the following information from the Query
Analyzer?
Number of Concurrent Connections/Users
Average Transaction Per/Sec
Database Growth Rate
RegardsHi,
You can querry system table master..sysprocesses and get required counters.
Amol Lembhe
"Praveen" wrote:
> HI All
> Could help me out to find the following information from the Query
> Analyzer?
> Number of Concurrent Connections/Users
> Average Transaction Per/Sec
> Database Growth Rate
> Regards
>

Concurrent Connections

HI All
Could help me out to find the following information from the Query
Analyzer?
Number of Concurrent Connections/Users
Average Transaction Per/Sec
Database Growth Rate
Regards
Hi,
You can querry system table master..sysprocesses and get required counters.
Amol Lembhe
"Praveen" wrote:

> HI All
> Could help me out to find the following information from the Query
> Analyzer?
> Number of Concurrent Connections/Users
> Average Transaction Per/Sec
> Database Growth Rate
> Regards
>