How to make SMO use TCP instead of NP for server connections?

We have ~30 servers that don't have Named Pipes enabled and I can't stop/start the services until the next Maintenance Window (30 days away). I need to query all the server for users and logins (Security Audit). I want to use SMO since working with its collections will save me a lot of coding. It seems that the default protocol for SMO is Named Pipes so I get connection failure for those servers that don't have NP enabled. Is there any way to have SMO use TCP instead of NP for connecting?

SMO uses the ConnectionManager class to create connections, this class assembles the connection string based on the passed variables. The connection will pick the protocol order the client has specified to connect to the server. If you want to force the connection to use Named Pipes, you might use:

Instead of just the servername use npervername

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||Thanks - I'll give a try, but I need to TCP so I think it would tcpervername.

|||

Yep, sure you are right, you should use (without smileys :-) )

Code Snippet

tcp:Servername

|||

It didn't work. I probably haven't set the variables up correctly. I found the ConnectionContext last night and plugged it in this morning.That works but I'd like to see how your syntax should be put together if you don't mind.

note - the forum software put the Smileys in. I typed a colon as I'm sure you did (this one is intentional)

Try

svrname = Trim(svrname)

svrname = "tcpERVERNAME"

Dim svr As Server = New Server(svrname)

'svr.ConnectionContext.NetworkProtocol = NetworkProtocol.TcpIp

For Each db In svr.Databases

If db.Status = 1 Then

....

...

|||Mea Culpa - It seems there was a typo in the server name. It does work. Thank you for your help.

How to login to SQL servers in a DTS?

Hello,

I have a DTS package in which I have several flows using different connections (OLE DB Providers, etc...) with Login ID and password.

But whenever the password changes, I need to manually update all the connections I have in this DTS. Isn't there an easier way to do that, somewhere via code or something else?
DTS or SSIS?

For DTS questions, see the DTS forum: http://groups.google.com/group/microsoft.public.sqlserver.dts?lnk=srg|||

Assuming you're talking about SSIS, you can consider other package protection levels (avoid password encryption) - e.g. ServerStorage.

See http://technet.microsoft.com/en-us/library/ms141747(SQL.90).aspx

How to login to SQL servers in a DTS?

Hello,

I have a DTS package in which I have several flows using different connections (OLE DB Providers, etc...) with Login ID and password.

But whenever the password changes, I need to manually update all the connections I have in this DTS. Isn't there an easier way to do that, somewhere via code or something else?
DTS or SSIS?

For DTS questions, see the DTS forum: http://groups.google.com/group/microsoft.public.sqlserver.dts?lnk=srg|||

Assuming you're talking about SSIS, you can consider other package protection levels (avoid password encryption) - e.g. ServerStorage.

See http://technet.microsoft.com/en-us/library/ms141747(SQL.90).aspx

How to limit number of connections in Analysis Services

Hi All,

Our users accesses the OLAP database using excel with large data reporting. When the number of connections reaches to 20-30, the generation of reports becomes very slow. My question is, is there a way in order to limit the number of connections that can access the olap, in this way we can use this approach to let user create their reports by batch with only let say 5 connections.

Thanks...

There is no property or knob allowing you to limit number of connections.

You can use different ways to scale your application. One: is to install several machines and configure them to run as NLB cluster. This way your users will be connecting to the same IP and NLB will distribute the load between several physical machines.

You can try and implement simple application based on the DISCOVER_CONNECTIONS request ( see ActivityViewer sample application for example of it) and starts cancelling any new connections after reaching some threashold.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

How to limit number of connections for a user

Is there a way to limit a user's number of connections?
I'm asking because I have a user who keeps more than 50 query windows in
query analyzer.
Thanks!At the client side no...
on the server side you could write a scrip that periodically looks at
master..sysprocesses and issues KILL commands for connections that you want
to close.
But there is no way to set a threshold that say 'this user has 10
connections already so don't allow number 11'. You'd have to kill number 11
after it happens...
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Michael" <michael.rodriguez@.loanperformance.com> wrote in message
news:u3OxQqlxDHA.3408@.tk2msftngp13.phx.gbl...
> Is there a way to limit a user's number of connections?
> I'm asking because I have a user who keeps more than 50 query windows in
> query analyzer.
> Thanks!
>|||Thanks for the reply Brian. I really appreciate it.
Unfortunately I'm looking for something better.
Maybe in future release of SQL server, they'll add that in there.
Thanks again!
"Brian Moran" <brian@.solidqualitylearning.com> wrote in message
news:OarcT%23lxDHA.3224@.tk2msftngp13.phx.gbl...
> At the client side no...
> on the server side you could write a scrip that periodically looks at
> master..sysprocesses and issues KILL commands for connections that you
want
> to close.
> But there is no way to set a threshold that say 'this user has 10
> connections already so don't allow number 11'. You'd have to kill number
11
> after it happens...
> --
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www.solidqualitylearning.com
>
> "Michael" <michael.rodriguez@.loanperformance.com> wrote in message
> news:u3OxQqlxDHA.3408@.tk2msftngp13.phx.gbl...
> > Is there a way to limit a user's number of connections?
> >
> > I'm asking because I have a user who keeps more than 50 query windows in
> > query analyzer.
> >
> > Thanks!
> >
> >
>|||Hi Michael,
Thank you for using MSDN Newsgroup!
Thank you for your feedback! I am regret that there is not a easier way to
meet your request in SQL Server 2000. However, the products of Microsoft is
always keep up with the current application and forsee the new application
in different fields. There are many new exciting and powerful features in
our new generation SQL Server-Yukon, which are based on our study of the
new application of the Database systems. You can refer the information of
Yukon at:
http://www.microsoft.com/sql/yukon/productinfo/default.asp
You feedback is helpful for our developer to produce the best products and
satisfy our customers include you and others using products of Microsoft.
Thank you very much! Merry Christmas and Happy New Year!
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.

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
>
>