link server reset connection

I have recently begun getting a sp_reset_connection on one linked
server. Both are SQL 2000 build 818 (SP3).
The login time between each reset is about 10 minutes. In the past the
login time was at least 24 to 48 hours.
I have rebooted both servers and it's still happening. Any ideas?
>>> On Sun, Nov 19, 2006 at 6:49 PM, in message
<1163987375.312919.163700@.e3g2000cwe.googlegroups. com>,
<bic1ster@.gmail.com> wrote:
> I have recently begun getting a sp_reset_connection on one linked
> server. Both are SQL 2000 build 818 (SP3).
> The login time between each reset is about 10 minutes. In the past
> the
> login time was at least 24 to 48 hours.
> I have rebooted both servers and it's still happening. Any ideas?
Did anything change on the servers or the network (such as a new
firewall between the hosts)?
|||I will check. I did look at the Compaq Insight NIC application and
they don't have any packet receive errors etc.. I also looked at event
logs and nothing pecular was in them. Maybe it's time for some
perfmon.
We did have a switch blade failure last month. I don't remember if
these were on that blade.
|||It's an undocumented stored procedure used internally by SQL
Server. It shows up when using connection pooling
and it is called to reset the connection options,settings,
etc before reusing the connection in the connection pool so
that the settings don't persist to another client
connection. In and of itself, just seeing it execute every
10 minutes doesn't necessarily indicate any problems. Why do
you have concerns? If the issue is that it's every 10 mins
now instead of 24 to 48 hours, you can run a trace and see
what the connection is executing to see what's going on with
whatever application, process. It may be nothing though.
-Sue
On 19 Nov 2006 17:49:35 -0800, bic1ster@.gmail.com wrote:

>I have recently begun getting a sp_reset_connection on one linked
>server. Both are SQL 2000 build 818 (SP3).
>The login time between each reset is about 10 minutes. In the past the
>login time was at least 24 to 48 hours.
>I have rebooted both servers and it's still happening. Any ideas?
|||> connection. In and of itself, just seeing it execute every
> 10 minutes doesn't necessarily indicate any problems. Why do
> you have concerns? If the issue is that it's every 10 mins
It wasn't happening two weeks ago as I monitor who is connected to the
server. ok, I will look at profiler for a while and see what happens.
I think the app was updated recently too. Maybe something else to
check with the developer.
sql

link server reset connection

I have recently begun getting a sp_reset_connection on one linked
server. Both are SQL 2000 build 818 (SP3).
The login time between each reset is about 10 minutes. In the past the
login time was at least 24 to 48 hours.
I have rebooted both servers and it's still happening. Any ideas?>>> On Sun, Nov 19, 2006 at 6:49 PM, in message
<1163987375.312919.163700@.e3g2000cwe.googlegroups.com>,
<bic1ster@.gmail.com> wrote:
> I have recently begun getting a sp_reset_connection on one linked
> server. Both are SQL 2000 build 818 (SP3).
> The login time between each reset is about 10 minutes. In the past
> the
> login time was at least 24 to 48 hours.
> I have rebooted both servers and it's still happening. Any ideas?
Did anything change on the servers or the network (such as a new
firewall between the hosts)?|||I will check. I did look at the Compaq Insight NIC application and
they don't have any packet receive errors etc.. I also looked at event
logs and nothing pecular was in them. Maybe it's time for some
perfmon.
We did have a switch blade failure last month. I don't remember if
these were on that blade.|||It's an undocumented stored procedure used internally by SQL
Server. It shows up when using connection pooling
and it is called to reset the connection options,settings,
etc before reusing the connection in the connection pool so
that the settings don't persist to another client
connection. In and of itself, just seeing it execute every
10 minutes doesn't necessarily indicate any problems. Why do
you have concerns? If the issue is that it's every 10 mins
now instead of 24 to 48 hours, you can run a trace and see
what the connection is executing to see what's going on with
whatever application, process. It may be nothing though.
-Sue
On 19 Nov 2006 17:49:35 -0800, bic1ster@.gmail.com wrote:

>I have recently begun getting a sp_reset_connection on one linked
>server. Both are SQL 2000 build 818 (SP3).
>The login time between each reset is about 10 minutes. In the past the
>login time was at least 24 to 48 hours.
>I have rebooted both servers and it's still happening. Any ideas?|||> connection. In and of itself, just seeing it execute every
> 10 minutes doesn't necessarily indicate any problems. Why do
> you have concerns? If the issue is that it's every 10 mins
It wasn't happening two weeks ago as I monitor who is connected to the
server. ok, I will look at profiler for a while and see what happens.
I think the app was updated recently too. Maybe something else to
check with the developer.

link server reset connection

I have recently begun getting a sp_reset_connection on one linked
server. Both are SQL 2000 build 818 (SP3).
The login time between each reset is about 10 minutes. In the past the
login time was at least 24 to 48 hours.
I have rebooted both servers and it's still happening. Any ideas?>> On Sun, Nov 19, 2006 at 6:49 PM, in message
<1163987375.312919.163700@.e3g2000cwe.googlegroups.com>,
<bic1ster@.gmail.com> wrote:
> I have recently begun getting a sp_reset_connection on one linked
> server. Both are SQL 2000 build 818 (SP3).
> The login time between each reset is about 10 minutes. In the past
> the
> login time was at least 24 to 48 hours.
> I have rebooted both servers and it's still happening. Any ideas?
Did anything change on the servers or the network (such as a new
firewall between the hosts)?|||I will check. I did look at the Compaq Insight NIC application and
they don't have any packet receive errors etc.. I also looked at event
logs and nothing pecular was in them. Maybe it's time for some
perfmon.
We did have a switch blade failure last month. I don't remember if
these were on that blade.|||It's an undocumented stored procedure used internally by SQL
Server. It shows up when using connection pooling
and it is called to reset the connection options,settings,
etc before reusing the connection in the connection pool so
that the settings don't persist to another client
connection. In and of itself, just seeing it execute every
10 minutes doesn't necessarily indicate any problems. Why do
you have concerns? If the issue is that it's every 10 mins
now instead of 24 to 48 hours, you can run a trace and see
what the connection is executing to see what's going on with
whatever application, process. It may be nothing though.
-Sue
On 19 Nov 2006 17:49:35 -0800, bic1ster@.gmail.com wrote:
>I have recently begun getting a sp_reset_connection on one linked
>server. Both are SQL 2000 build 818 (SP3).
>The login time between each reset is about 10 minutes. In the past the
>login time was at least 24 to 48 hours.
>I have rebooted both servers and it's still happening. Any ideas?|||> connection. In and of itself, just seeing it execute every
> 10 minutes doesn't necessarily indicate any problems. Why do
> you have concerns? If the issue is that it's every 10 mins
It wasn't happening two weeks ago as I monitor who is connected to the
server. ok, I will look at profiler for a while and see what happens.
I think the app was updated recently too. Maybe something else to
check with the developer.

Link server not working thru Windows authentication - "Login failed

I've two SQL server 2005 servers running under the same domain and the
SQL server service is also running under domain adminstrator for both
the servers. The OS on both the servers is Win2K3.
All the developers were working on these servers as 'sa' but recently
we have changed it to run on windows authentications. In doing so I
added all the developer's domain logins to a local group "Power Users"
on both the servers and now every body can log on to these servers
using their domain logins(windows authentication). But the link server
from serv1 to serv2 is giving error "Msg 18456, Level 14, State 1,
Line 1" "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'." if I
run a distributed querry from QA.
I've also tried adding individual logins on the Security tab on link
server gui and checked impersonate check box also but with no results.
On both the servers "NT AUTHORITY\NETWORK SERVICE" account exists with
sysadmin as server role.
One strange thing is if I directly logon (using my login) to the serv1
and run a distributed querry using the same link server it works.
Any help will be appriciated
Thanks,
SGsaboo,
I believe that you have a delegation problem here. Is Kerberos running in
your domain and are both servers correctly set up to user Kerboros? Read
this for an explanation:
http://blogs.msdn.com/sql_protocols/archive/2006/08/10/694657.aspx
When you directly login to serv1 you are now only one hop away from serv2
and delegation is not an issue. But, from a desktop to serv2 is two hops.
RLF
"saboo" <subodh97@.gmail.com> wrote in message
news:c210f136-752a-445a-94cf-b05599286972@.j20g2000hsi.googlegroups.com...
> I've two SQL server 2005 servers running under the same domain and the
> SQL server service is also running under domain adminstrator for both
> the servers. The OS on both the servers is Win2K3.
> All the developers were working on these servers as 'sa' but recently
> we have changed it to run on windows authentications. In doing so I
> added all the developer's domain logins to a local group "Power Users"
> on both the servers and now every body can log on to these servers
> using their domain logins(windows authentication). But the link server
> from serv1 to serv2 is giving error "Msg 18456, Level 14, State 1,
> Line 1" "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'." if I
> run a distributed querry from QA.
> I've also tried adding individual logins on the Security tab on link
> server gui and checked impersonate check box also but with no results.
> On both the servers "NT AUTHORITY\NETWORK SERVICE" account exists with
> sysadmin as server role.
> One strange thing is if I directly logon (using my login) to the serv1
> and run a distributed querry using the same link server it works.
> Any help will be appriciated
> Thanks,
> SG|||On Feb 13, 2:40=A0pm, "Russell Fields" <russellfie...@.nomail.com> wrote:
> saboo,
> I believe that you have a delegation problem here. =A0Is Kerberos running =in
> your domain and are both servers correctly set up to user Kerboros? =A0Rea=d
> this for an explanation:http://blogs.msdn.com/sql_protocols/archive/2006/0=
8/10/694657.aspx
> When you directly login to serv1 you are now only one hop away from serv2
> and delegation is not an issue. =A0But, from a desktop to serv2 is two hop=s.
> RLF
> "saboo" <subod...@.gmail.com> wrote in message
> news:c210f136-752a-445a-94cf-b05599286972@.j20g2000hsi.googlegroups.com...
>
> > I've two SQL server 2005 servers running under the same domain and the
> > SQL server service is also running under domain adminstrator for both
> > the servers. The OS on both the servers is Win2K3.
> > All the developers were working on these servers as 'sa' but recently
> > we have changed it to run on windows authentications. In doing so I
> > added all the developer's domain logins to a local group "Power Users"
> > on both the servers and now every body can log on to these servers
> > using their domain logins(windows authentication). But the link server
> > from serv1 to serv2 is giving error "Msg 18456, Level 14, State 1,
> > Line 1" "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'." if I
> > run a distributed querry from QA.
> > I've also tried adding individual logins on the Security tab on link
> > server gui and checked impersonate check box also but with no results.
> > On both the servers "NT AUTHORITY\NETWORK SERVICE" account exists with
> > sysadmin as server role.
> > One strange thing is if I directly logon (using my login) to the serv1
> > and run a distributed querry using the same link server it works.
> > Any help will be appriciated
> > Thanks,
> > SG- Hide quoted text -
> - Show quoted text -
Delegation isn't too bad to deal with. Most likely your SQL servers
are not using a Local Service account to run (good for you, this is
not good security) and the accounts it's using are not domain admins
(again, good job). Here's the quick down-low on what you need to do:
You need domain admin rights to create an SPN, if you don't have them
you need to get your DA to do this for you. Download the Windows
Server tools for your flavor of server(http://go.microsoft.com/fwlink/?
LinkId=3D100114). You want the SetSPN tool. You need to create an SPN
for each of the SQL servers, this can be done from your workstation as
it's set Domain wide, not at the server level.
setspn -a service/name hostname
i.e.
setspn -a MSSQLsvc/my.server.name:1433 mydomain\MySqlServiceAccount
Again, do this for BOTH sql servers...sql

Link Server

Can u have a link server from SQL 7 to SQL 2000. I keep getting login error but I can connect successfully using query analyzer
thx
In the security tab in the linked server select the option: Be made using
this security context and put in the sa account and password. If that works
you can configure a standard SQL login to use.
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||that is how I have it but I get 'login failed for sa' and I know the passwd is right

Link Server

Can u have a link server from SQL 7 to SQL 2000. I keep getting login error
but I can connect successfully using query analyzer
thxIn the security tab in the linked server select the option: Be made using
this security context and put in the sa account and password. If that works
you can configure a standard SQL login to use.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||that is how I have it but I get 'login failed for sa' and I know the passwd
is rightsql

Limiting data access based on login information ?

Here is what I want to do : we have on our network 250 stores. I want them to be able to access their P&L, sales, old invoices,... The trick is that I want each store to access his and only his information, without designing 250 times the same report with the store number as a preset value. Is there a way with SSRS to preset the "@.Store" parameter value based upon the login information ?

For this case I would implement row level security, because even if the parameter would be preset, user could gain access to other reports using the URl parameters. Therefore a implemented row level security, which filters out the appropiate value from the results with passing the Windows credentials would help you much more than that.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

Thks for your answer Jens. I m still quite a newbie with SQL... What are the basic steps to implement row level security ? Do you have to do it in your DB serving as datasource, or in the DB supporting SSRS ?

|||After understanding the principle of that , it should be easy for you to implement:

http://www.google.de/search?hl=de&q=row+level+security+sql+server&meta=

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Limited Remote Login db access

I have connected my desktop and laptop computer at home, on my desktop I have a series of db's under my default named instance <computername>/sqlexpress and I have created access logins using windows auth. When I connect to the desktop from the laptop using tcp:<computername>\sqlexpress\userid I can only see the system db's...I am trying to view adventure works, which I can see on the desktop fine using the same login...any ideas?

Thanks,

Rob

Do you have a domain controller and home network?

If not, then you must create use SQL logins -you cannot use Windows authentication without an Active Directory (domain controller).

|||

I don't even know what those things mean.

How would I go about doing this with SQL logins?

Rob

|||

I suggest you follow the steps under "To configure AdventureWorks to work with Web server controls" on the following page from the Books Online.

http://msdn2.microsoft.com/en-us/library/ms310325(vs.80).aspx

Hopefully this helps,

John

|||

So I read over the link provided...

I have completed the first part and added it to the server. I can access the database on my desktop machine, when I login from the laptop however (using the same windows login) I can't access to database...do I need to also add something to make it accessible remotely? I don't really understand what "configure AdventureWorks to work with Web server controls
" means..

Rob

|||

These resources might help.

Configuration -Configure SQL Server 2005 to allow remote connections
http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277

Configuration -Connect to SQL Express from "downlevel clients"
http://blogs.msdn.com/sqlexpress/archive/2004/07/23/192044.aspx

|||

I have done this...as I stated before I can connect fine its just that I can't see anything other than system db's...I imagine I'm missing something stupid, like perhaps I didn't configure the login correctly?

Rob

|||

The login needs to be provided permission to access the databases.

Add the login to the appropriate database role.

|||

Ok first...I am running XP home on both the desktop and laptop...and the logins are windows auth.

if I want full access from the remote connection, where do I change the role of the login? is it in the login menu or the databases menu?

Rob

|||

Look back at the first response given to your problem.

Windows authentication will not work in your situation -you do not have a domain controller.

You will need to establish SQL Logins in each server, and assign permissions accordingly.

Look in Books Online about creating SQL Logins.

Limited Remote Login access

Hello all,

I'm very new to this so excuse my naiveity...I have connected my desktop and laptop computer at home, on my desktop I have a series of db's under my default named instance <computername>/sqlexpress and I have created access logins using windows auth. When I connect to the desktop from the laptop using tcp:<computername>\sqlexpress\userid I can only see the systemdb's...I am trying to view adventure works, just to see how this remote connection stuff works...any ideas?

Thanks,

Rob

See my response to your post in the 'Data Access' forum.

limit the access

Hi, guys!

Some of my applications are sharing same SQL login/password to connect
to a database called "MyDB" on server "MyServer" . The password is
encrypted and stored in registry or some configuration file the
applications use. The applications use certain arithmetic to decrypt
the password and then connect to MyDB.

The problem is a few developers know the arithmetic. So virtually
there is no security here.

I am wondering whether I can do anything on the MyServer/MyDB to limit
the access to the database so that only connection from certain
servers are allowed. Say I only want connection with this known
credential to be established if it is from server "Mybox". No
connections from any other servers will be allowed. So even the
developers know the login/password, they won't be able to do anything
if they do have the access to server "MyBox".
(I know some of you would ask why I don't use application roles. Let's
say it's due to "historical" reasons and it's not totally up to me to
change the way the developers use database.)

Any idea? Triggers in Master? Not a good idea, isn't it?

Thanks in advance,

Gary"Gary" <rooty_hill2002@.yahoo.com.au> wrote in message
news:171bd226.0410062159.441a405b@.posting.google.c om...
> Hi, guys!
> Some of my applications are sharing same SQL login/password to connect
> to a database called "MyDB" on server "MyServer" . The password is
> encrypted and stored in registry or some configuration file the
> applications use. The applications use certain arithmetic to decrypt
> the password and then connect to MyDB.
> The problem is a few developers know the arithmetic. So virtually
> there is no security here.
> I am wondering whether I can do anything on the MyServer/MyDB to limit
> the access to the database so that only connection from certain
> servers are allowed. Say I only want connection with this known
> credential to be established if it is from server "Mybox". No
> connections from any other servers will be allowed. So even the
> developers know the login/password, they won't be able to do anything
> if they do have the access to server "MyBox".
> (I know some of you would ask why I don't use application roles. Let's
> say it's due to "historical" reasons and it's not totally up to me to
> change the way the developers use database.)
> Any idea? Triggers in Master? Not a good idea, isn't it?
> Thanks in advance,
> Gary

Well, if you want real security then at a minimum you need to stop using
shared logins. Create a login for each user and developer, or use Windows
security which is generally preferred, create roles with limited permissions
etc. This is the standard best practice for MSSQL security:

http://www.microsoft.com/technet/pr...n/sp3sec00.mspx

It sounds as if you're trying to hack something in, rather than step back
and fix the fundamental problems. If others in the organization claim it's
too much work, too restrictive etc. then make sure that the business users
and your boss know there is no security in place to prevent abuse of the
system - if they don't care, then fine, but make sure you get that in
writing... Assuming they do care, then you should be able to get the
authority to fix the situation.

To answer your original question, triggers on system tables aren't
supported, and the sysprocesses table which shows current connections isn't
a physical table anyway, it's a fake one which is created when you query it.
You could create a scheduled job which runs every few seconds, and KILLs any
SPIDs which are not from authorized hosts (using the HOST_NAME() function),
but that's really a nasty kludge, not a proper solution.

Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message news:<41652f1c$1_1@.news.bluewin.ch>...
> "Gary" <rooty_hill2002@.yahoo.com.au> wrote in message
> news:171bd226.0410062159.441a405b@.posting.google.c om...
> > Hi, guys!
> > Some of my applications are sharing same SQL login/password to connect
> > to a database called "MyDB" on server "MyServer" . The password is
> > encrypted and stored in registry or some configuration file the
> > applications use. The applications use certain arithmetic to decrypt
> > the password and then connect to MyDB.
> > The problem is a few developers know the arithmetic. So virtually
> > there is no security here.
> > I am wondering whether I can do anything on the MyServer/MyDB to limit
> > the access to the database so that only connection from certain
> > servers are allowed. Say I only want connection with this known
> > credential to be established if it is from server "Mybox". No
> > connections from any other servers will be allowed. So even the
> > developers know the login/password, they won't be able to do anything
> > if they do have the access to server "MyBox".
> > (I know some of you would ask why I don't use application roles. Let's
> > say it's due to "historical" reasons and it's not totally up to me to
> > change the way the developers use database.)
> > Any idea? Triggers in Master? Not a good idea, isn't it?
> > Thanks in advance,
> > Gary
> Well, if you want real security then at a minimum you need to stop using
> shared logins. Create a login for each user and developer, or use Windows
> security which is generally preferred, create roles with limited permissions
> etc. This is the standard best practice for MSSQL security:
> http://www.microsoft.com/technet/pr...n/sp3sec00.mspx
> It sounds as if you're trying to hack something in, rather than step back
> and fix the fundamental problems.

Simon,

Thanks for this.

I think I am pretty familiar with the standard practice and you are
right that I don't have any chance (at least at this moment) to
rollback what the developers are doing so I can standardize the way of
database use.

If others in the organization claim it's
> too much work,

Yes they certainly do!

too restrictive etc. then make sure that the business users
> and your boss know there is no security in place to prevent abuse of the
> system - if they don't care, then fine, but make sure you get that in
> writing...

Good idea. I will try.

Assuming they do care, then you should be able to get the
> authority to fix the situation.

Again, they also DO care. That is why some "temporary solution" is
required
-:)... The good thing is I have been doing coding for more than 12
years so I know most of the tricks they have. I also have been using
MSSQL for about 7 years (not 24*7 DBA though). So I am now in a
position that I feel I know engouh to tell how bad they (including me)
are doing in regard to security while yet I don't know enough to come
up with this temporary solution for them.

> To answer your original question, triggers on system tables aren't
> supported, and the sysprocesses table which shows current connections isn't
> a physical table anyway, it's a fake one which is created when you query it.
> You could create a scheduled job which runs every few seconds, and KILLs any
> SPIDs which are not from authorized hosts (using the HOST_NAME() function),
> but that's really a nasty kludge, not a proper solution.

It is a pity we can't use supported database level triggers here. For
our Oracle databases, I have actually done this easily. Well, I will
continue to try before I get the mandate to enforce the proper way of
accessing database via applications in this company.

Thanks again.
> Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message
news:41652f1c$1_1@.news.bluewin.ch...
> "Gary" <rooty_hill2002@.yahoo.com.au> wrote in message
> news:171bd226.0410062159.441a405b@.posting.google.c om...
>> Hi, guys!
>>
>> Some of my applications are sharing same SQL login/password to connect
>> to a database called "MyDB" on server "MyServer" . The password is
>> encrypted and stored in registry or some configuration file the
>> applications use. The applications use certain arithmetic to decrypt
>> the password and then connect to MyDB.
>>
>> The problem is a few developers know the arithmetic. So virtually
>> there is no security here.
>>
>> I am wondering whether I can do anything on the MyServer/MyDB to limit
>> the access to the database so that only connection from certain
>> servers are allowed.

<snip
Aside from everything else already mentioned, if you really want to limit
access to particular MACHINES/SERVERS, you may consider placing the SQL
Server behind a hardware- or software-based firewall and only opening the
appropriate ports to the appropriate addresses.