Cluster failing connections with SSL error message

We recently migrated our production environment from a win2k3/SQL 2000 EE cluster to a new 64 bit win2k3/ SQL 2005 SP1 Cluster.

Cluster works fine for a while (3-6 hours), then our logins start failing. We recieve the following error in the event log:

"The server was unable to load the SSL provider library needed to log in; the connection has been closed. SSL is used to encrypt either the login sequence or all communications, depending on how the administrator has configured the server. See Books Online for information on this error message"

The only way to resume connectivity is to offline and online the sql service in the cluster.

The only reference to this error that I can find has something to do with a hotfix that might be available?

It's sad to say but we are experiencing the same issue.
Only we get this after a good 30 minutes...

Someone from Microsoft Spain is flying over here.

Very painful

|||

In working with microsoft PSS, we found the issue to be a permissions issue of all things.

Our service account did not have rights to lock pages in memory on either machines in the cluster. (even though is a domain administrator).

Once we gave it this right and restarted the SQL Server object in the cluster we have not had a similar failure since.

|||

This was unfortunately enabled already so it did not cause the problem here :-(

|||Hi Wesley,

Any joy with this? I'm currently going throught the same thing, and also have locked pages enabled for my SQL service account. I'm currently going through MS Support, but seem to be going around the houses :o(

Thanks

Stu|||Any luck, we have a similar problem.
We noticed that memory usage grows slowly beyond physical memory (32G) then connection failiurs start and server becomes inoperable.
Workaround solution is to reduce max mem in the server, down to 18G from (25).
This causes the server and OS to release memory. After the system stabilized we increased the maxmem in the server back to its original settng. Lock pages in memory is set.

Hope this helps.

This is a workaround, so we don't have to bounce the server, we are still looking for the cause.
esk.

Setup:2 node Cluster 4*dual 64bit AMD, 32Gb, SQL 2005 Enterprise SP0.|||

Hi,

Apparently we have hit a bug where the usercache size becomes too large and for every execution of a statement SQL Server has to check this store.
This store is not cleaned and keep growing too the point where lookups become blocked with spinlocks. If you want I can give you the commands to check this.

This will be fixed in SP2 because of the major impact a fix would have. The workaround is adding the users to the sysadmin group or clearing the cache manually with DBCC FREESYSTEMCACHE.

HTH

Kind regards,
Wesley

|||Thanks for the replies !

Wes, if you could give the command to check the spinlocks that would be great.

many thanks

Stu|||

Stu,

It's actually the query for checking the cache size.

SELECT * FROM sys.dm_os_memory_clerks
WHERE type = 'USERSTORE_TOKENPERM'
We have seen problems as of 150MB in our environment but this probably all depends on the load and types of queries. Add up the single pages and multipages to determine the size.

HTH
Wesley

|||Thanks Wesley!

After much investigation it turned out the problem was the iLO Management Channel Interface Driver (Cpqcidrv.sys) from Hewlett-Packard. Apparently this

is known to cause this issue on x64 editions of SQL Server 2005. The driver

causes the SQL Server 2005 64-bit working set to be trimmed!


We do not use ILO on our HP Servers, so I disable it in device manager, and so far so good (fingers crossed)!!!

Thanks

Stu|||

Great news!

There seems to be a hotfix for Windows 2003 concerning working set trims too.
Apparently working sets get trimmed when you use Terminal Services on the server... so beware ;-)

|||

If possible could you share the hotfix number? Thanks in advance,

Drew

|||

Hey Wesley. Can you please elaborate more on this 'working sets get trimmed' and beware. and the hotfix available? Thank you very much. I've had problems with this also.

|||

I am also have a problem with this issue. I am going to try disabling the ILO. The terminal service hotfix seems to be this one:

http://support.microsoft.com/kb/905865/en-us

sqlsql

Cluster failing connections with SSL error message

We recently migrated our production environment from a win2k3/SQL 2000 EE cluster to a new 64 bit win2k3/ SQL 2005 SP1 Cluster.

Cluster works fine for a while (3-6 hours), then our logins start failing. We recieve the following error in the event log:

"The server was unable to load the SSL provider library needed to log in; the connection has been closed. SSL is used to encrypt either the login sequence or all communications, depending on how the administrator has configured the server. See Books Online for information on this error message"

The only way to resume connectivity is to offline and online the sql service in the cluster.

The only reference to this error that I can find has something to do with a hotfix that might be available?

It's sad to say but we are experiencing the same issue.
Only we get this after a good 30 minutes...

Someone from Microsoft Spain is flying over here.

Very painful

|||

In working with microsoft PSS, we found the issue to be a permissions issue of all things.

Our service account did not have rights to lock pages in memory on either machines in the cluster. (even though is a domain administrator).

Once we gave it this right and restarted the SQL Server object in the cluster we have not had a similar failure since.

|||

This was unfortunately enabled already so it did not cause the problem here :-(

|||Hi Wesley,

Any joy with this? I'm currently going throught the same thing, and also have locked pages enabled for my SQL service account. I'm currently going through MS Support, but seem to be going around the houses :o(

Thanks

Stu|||Any luck, we have a similar problem.
We noticed that memory usage grows slowly beyond physical memory (32G) then connection failiurs start and server becomes inoperable.
Workaround solution is to reduce max mem in the server, down to 18G from (25).
This causes the server and OS to release memory. After the system stabilized we increased the maxmem in the server back to its original settng. Lock pages in memory is set.

Hope this helps.

This is a workaround, so we don't have to bounce the server, we are still looking for the cause.
esk.

Setup:2 node Cluster 4*dual 64bit AMD, 32Gb, SQL 2005 Enterprise SP0.|||

Hi,

Apparently we have hit a bug where the usercache size becomes too large and for every execution of a statement SQL Server has to check this store.
This store is not cleaned and keep growing too the point where lookups become blocked with spinlocks. If you want I can give you the commands to check this.

This will be fixed in SP2 because of the major impact a fix would have. The workaround is adding the users to the sysadmin group or clearing the cache manually with DBCC FREESYSTEMCACHE.

HTH

Kind regards,
Wesley

|||Thanks for the replies !

Wes, if you could give the command to check the spinlocks that would be great.

many thanks

Stu|||

Stu,

It's actually the query for checking the cache size.

SELECT * FROM sys.dm_os_memory_clerks
WHERE type = 'USERSTORE_TOKENPERM'
We have seen problems as of 150MB in our environment but this probably all depends on the load and types of queries. Add up the single pages and multipages to determine the size.

HTH
Wesley

|||Thanks Wesley!

After much investigation it turned out the problem was the iLO Management Channel Interface Driver (Cpqcidrv.sys) from Hewlett-Packard. Apparently this

is known to cause this issue on x64 editions of SQL Server 2005. The driver

causes the SQL Server 2005 64-bit working set to be trimmed!


We do not use ILO on our HP Servers, so I disable it in device manager, and so far so good (fingers crossed)!!!

Thanks

Stu|||

Great news!

There seems to be a hotfix for Windows 2003 concerning working set trims too.
Apparently working sets get trimmed when you use Terminal Services on the server... so beware ;-)

|||

If possible could you share the hotfix number? Thanks in advance,

Drew

|||

Hey Wesley. Can you please elaborate more on this 'working sets get trimmed' and beware. and the hotfix available? Thank you very much. I've had problems with this also.

|||

I am also have a problem with this issue. I am going to try disabling the ILO. The terminal service hotfix seems to be this one:

http://support.microsoft.com/kb/905865/en-us

Cluster failing connections with SSL error message

We recently migrated our production environment from a win2k3/SQL 2000 EE cluster to a new 64 bit win2k3/ SQL 2005 SP1 Cluster.

Cluster works fine for a while (3-6 hours), then our logins start failing. We recieve the following error in the event log:

"The server was unable to load the SSL provider library needed to log in; the connection has been closed. SSL is used to encrypt either the login sequence or all communications, depending on how the administrator has configured the server. See Books Online for information on this error message"

The only way to resume connectivity is to offline and online the sql service in the cluster.

The only reference to this error that I can find has something to do with a hotfix that might be available?

It's sad to say but we are experiencing the same issue.
Only we get this after a good 30 minutes...

Someone from Microsoft Spain is flying over here.

Very painful

|||

In working with microsoft PSS, we found the issue to be a permissions issue of all things.

Our service account did not have rights to lock pages in memory on either machines in the cluster. (even though is a domain administrator).

Once we gave it this right and restarted the SQL Server object in the cluster we have not had a similar failure since.

|||

This was unfortunately enabled already so it did not cause the problem here :-(

|||Hi Wesley,

Any joy with this? I'm currently going throught the same thing, and also have locked pages enabled for my SQL service account. I'm currently going through MS Support, but seem to be going around the houses :o(

Thanks

Stu|||Any luck, we have a similar problem.
We noticed that memory usage grows slowly beyond physical memory (32G) then connection failiurs start and server becomes inoperable.
Workaround solution is to reduce max mem in the server, down to 18G from (25).
This causes the server and OS to release memory. After the system stabilized we increased the maxmem in the server back to its original settng. Lock pages in memory is set.

Hope this helps.

This is a workaround, so we don't have to bounce the server, we are still looking for the cause.
esk.

Setup:2 node Cluster 4*dual 64bit AMD, 32Gb, SQL 2005 Enterprise SP0.|||

Hi,

Apparently we have hit a bug where the usercache size becomes too large and for every execution of a statement SQL Server has to check this store.
This store is not cleaned and keep growing too the point where lookups become blocked with spinlocks. If you want I can give you the commands to check this.

This will be fixed in SP2 because of the major impact a fix would have. The workaround is adding the users to the sysadmin group or clearing the cache manually with DBCC FREESYSTEMCACHE.

HTH

Kind regards,
Wesley

|||Thanks for the replies !

Wes, if you could give the command to check the spinlocks that would be great.

many thanks

Stu|||

Stu,

It's actually the query for checking the cache size.

SELECT * FROM sys.dm_os_memory_clerks
WHERE type = 'USERSTORE_TOKENPERM'
We have seen problems as of 150MB in our environment but this probably all depends on the load and types of queries. Add up the single pages and multipages to determine the size.

HTH
Wesley

|||Thanks Wesley!

After much investigation it turned out the problem was the iLO Management Channel Interface Driver (Cpqcidrv.sys) from Hewlett-Packard. Apparently this

is known to cause this issue on x64 editions of SQL Server 2005. The driver

causes the SQL Server 2005 64-bit working set to be trimmed!


We do not use ILO on our HP Servers, so I disable it in device manager, and so far so good (fingers crossed)!!!

Thanks

Stu|||

Great news!

There seems to be a hotfix for Windows 2003 concerning working set trims too.
Apparently working sets get trimmed when you use Terminal Services on the server... so beware ;-)

|||

If possible could you share the hotfix number? Thanks in advance,

Drew

|||

Hey Wesley. Can you please elaborate more on this 'working sets get trimmed' and beware. and the hotfix available? Thank you very much. I've had problems with this also.

|||

I am also have a problem with this issue. I am going to try disabling the ILO. The terminal service hotfix seems to be this one:

http://support.microsoft.com/kb/905865/en-us

Cluster failing connections with SSL error message

We recently migrated our production environment from a win2k3/SQL 2000 EE cluster to a new 64 bit win2k3/ SQL 2005 SP1 Cluster.

Cluster works fine for a while (3-6 hours), then our logins start failing. We recieve the following error in the event log:

"The server was unable to load the SSL provider library needed to log in; the connection has been closed. SSL is used to encrypt either the login sequence or all communications, depending on how the administrator has configured the server. See Books Online for information on this error message"

The only way to resume connectivity is to offline and online the sql service in the cluster.

The only reference to this error that I can find has something to do with a hotfix that might be available?

It's sad to say but we are experiencing the same issue.
Only we get this after a good 30 minutes...

Someone from Microsoft Spain is flying over here.

Very painful

|||

In working with microsoft PSS, we found the issue to be a permissions issue of all things.

Our service account did not have rights to lock pages in memory on either machines in the cluster. (even though is a domain administrator).

Once we gave it this right and restarted the SQL Server object in the cluster we have not had a similar failure since.

|||

This was unfortunately enabled already so it did not cause the problem here :-(

|||Hi Wesley,

Any joy with this? I'm currently going throught the same thing, and also have locked pages enabled for my SQL service account. I'm currently going through MS Support, but seem to be going around the houses :o(

Thanks

Stu|||Any luck, we have a similar problem.
We noticed that memory usage grows slowly beyond physical memory (32G) then connection failiurs start and server becomes inoperable.
Workaround solution is to reduce max mem in the server, down to 18G from (25).
This causes the server and OS to release memory. After the system stabilized we increased the maxmem in the server back to its original settng. Lock pages in memory is set.

Hope this helps.

This is a workaround, so we don't have to bounce the server, we are still looking for the cause.
esk.

Setup:2 node Cluster 4*dual 64bit AMD, 32Gb, SQL 2005 Enterprise SP0.|||

Hi,

Apparently we have hit a bug where the usercache size becomes too large and for every execution of a statement SQL Server has to check this store.
This store is not cleaned and keep growing too the point where lookups become blocked with spinlocks. If you want I can give you the commands to check this.

This will be fixed in SP2 because of the major impact a fix would have. The workaround is adding the users to the sysadmin group or clearing the cache manually with DBCC FREESYSTEMCACHE.

HTH

Kind regards,
Wesley

|||Thanks for the replies !

Wes, if you could give the command to check the spinlocks that would be great.

many thanks

Stu|||

Stu,

It's actually the query for checking the cache size.

SELECT * FROM sys.dm_os_memory_clerks
WHERE type = 'USERSTORE_TOKENPERM'
We have seen problems as of 150MB in our environment but this probably all depends on the load and types of queries. Add up the single pages and multipages to determine the size.

HTH
Wesley

|||Thanks Wesley!

After much investigation it turned out the problem was the iLO Management Channel Interface Driver (Cpqcidrv.sys) from Hewlett-Packard. Apparently this

is known to cause this issue on x64 editions of SQL Server 2005. The driver

causes the SQL Server 2005 64-bit working set to be trimmed!


We do not use ILO on our HP Servers, so I disable it in device manager, and so far so good (fingers crossed)!!!

Thanks

Stu|||

Great news!

There seems to be a hotfix for Windows 2003 concerning working set trims too.
Apparently working sets get trimmed when you use Terminal Services on the server... so beware ;-)

|||

If possible could you share the hotfix number? Thanks in advance,

Drew

|||

Hey Wesley. Can you please elaborate more on this 'working sets get trimmed' and beware. and the hotfix available? Thank you very much. I've had problems with this also.

|||

I am also have a problem with this issue. I am going to try disabling the ILO. The terminal service hotfix seems to be this one:

http://support.microsoft.com/kb/905865/en-us

Closing all open connections/sessions

Hi all
Is there a way to close all open connections/sessions to my db at a certain
time? I have a application that uses a web browser to access my database. At
the end of the day i will have loads of open connections/sessions to my db.
My plan was to run a job or script in the eveing to close all
connections/sessions. I have been doing it by going to EM and kill all the
open connections/sessions 1 by 1 but, it is tiring as at times i have 700
open connections/sessions and they keep accumulating everyday. Thank you in
advance.You should address the problem in the application itself and have it close
the unused connections, rather than kill them at the end of the day on the
server.
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"MittyKom" <MittyKom@.discussions.microsoft.com> wrote in message
news:DAF29302-3EE5-4E92-AE0F-CA59A1049BF6@.microsoft.com...
> Hi all
> Is there a way to close all open connections/sessions to my db at a
> certain
> time? I have a application that uses a web browser to access my database.
> At
> the end of the day i will have loads of open connections/sessions to my
> db.
> My plan was to run a job or script in the eveing to close all
> connections/sessions. I have been doing it by going to EM and kill all the
> open connections/sessions 1 by 1 but, it is tiring as at times i have 700
> open connections/sessions and they keep accumulating everyday. Thank you
> in
> advance.|||Try set db to single_user, then back to multi_user. Search BOL for details.
James
"MittyKom" wrote:

> Hi all
> Is there a way to close all open connections/sessions to my db at a certai
n
> time? I have a application that uses a web browser to access my database.
At
> the end of the day i will have loads of open connections/sessions to my db
.
> My plan was to run a job or script in the eveing to close all
> connections/sessions. I have been doing it by going to EM and kill all the
> open connections/sessions 1 by 1 but, it is tiring as at times i have 700
> open connections/sessions and they keep accumulating everyday. Thank you i
n
> advance.

Closing all connections

How do I set up a job so that I close all open database connections? I think we have a leak in our code which causes our DB to go down (max connections used) roughly once every month, so we just restart the SQL server. Until we can find the exact problem I'd like to do this.

For simplicities sake let's say my database name is just "test."

Thank you.I've seen 2 ways to do this:

1) an ALTER DATABASE command, setting the database into single user mode and kicking out all connections

ALTER DATABASE Test
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE

followed by a

ALTER DATABASE Test
SET MULTI_USER

2) a stored procedure which loops through a cursor of all connections and KILLs them. For example code, look here:Kill all the Active Connections to Your Database.

Terri|||Thank you for your help. Sorry for the immensely delayed response. Things have been far beyond hectic lately.

As far as method 2, do you know how to check how many connections are active? I wanna know if killing it actually DID close the connections.|||I believe you can use sp_who to see this information, and sp_who2 to see more detail.

Terri|||Thank you very much tmorton.

Close existing connection before deleting/restore database

I want to be able to force a restore or a delete on a database even if there still have some active connections. In SQL Server management studio there is a checkbox when you're trying to delete a database to "Close existing connection" i want to do the same thing but using SMO. How can i do this ?

ThankHi,

see my blog entry here:

http://sqlserver2005.de/SQLServer2005/MyBlog/tabid/56/EntryID/9/Default.aspx

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Close all open connections

Before I can drop an mdf file form the server, all connections needs to be closed. how can I force to close this connection. The solution explained on this blog don't seems to work in my case http://sqlserver2005.de/SQLServer2005/MyBlog/tabid/56/EntryID/9/Default.aspx

I'm using SQL express, with visual studio pro 2005.

Thx for you quick responses

best regards

Luc N

please verify the code I've used

Dim svr As Server

svr = Nothing

svr = New Server(".\SQLEXPRESS")

'attach database

Dim con As New SqlConnection(svr.ConnectionContext.ConnectionString)

Dim com As New SqlCommand("sp_detach_db", con)

Dim d As Database

con.Open()

For Each d In svr.Databases

If Not d.IsSystemObject Then

'com.CommandType = Data.CommandType.StoredProcedure

'com.Parameters.Add(New SqlParameter("@.dbname", d.Name))

'com.ExecuteNonQuery()

'MsgBox(d.UserName.ToString())

d.DatabaseOptions.UserAccess = DatabaseUserAccess.Restricted

SqlConnection.ClearAllPools()

d.Drop()

'com.CommandText = "DROP DATABASE " & d.Name.ToString

'com.CommandType = CommandType.Text

'com.ExecuteNonQuery()

'End If

End If

Next

com.Connection.Close()

Why do you think that the information form the blog is not working for you ?

Jens K. Suessmeyer

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

I've still got the message 'cannot drop the database because.......................'

I've modified my code , which seems to be working,

please reply your comments on this code

thx Luc

Try

Dim svr As Server

svr = Nothing

svr = New Server(".\SQLEXPRESS")

'attach database

Dim con As New SqlConnection(svr.ConnectionContext.ConnectionString)

Dim d As Database

con.Open()

For Each d In svr.Databases

If Not d.IsSystemObject Then

Exit For

End If

Next

If InStr(d.Name, "exp", CompareMethod.Text) <> 0 Then

svr.KillAllProcesses(d.Name)

d.DatabaseOptions.UserAccess = DatabaseUserAccess.Restricted

SqlConnection.ClearAllPools()

d.Drop()

con.Close()

End If

Catch ex As Exception

' MsgBox(ex.Message)

End Try

|||As I pointed out in the comment, they fixed the behaviour in the Service Pack, so KillDatabase should work for you.

close all existing connections and processes to a database

Dear all

I created this trigger on a table that i think failed while execution. I tried to modify it and run it again but it seems that i cant do that. If i try and delete the database i also cant - saying that it is still in use. But i am not using it and ther are no other users connected to it. I think the trigger has probably hit a loop and that is holding the link.

To close that i know that a solution would be to restart the SQL server instance but that would be a bit hard since the SQL server where my test database resides is a production server and has few other databases that are important and few users use them.

Is there any way through a SQL statement that there can be forced a delete? Or force close all the connections? Or force close all the processes without actually restarting the SQL server instance.

I have tried all options that were offered on some other forums like forcing it to a single user but even that operation can not be performed saying that the database is still in use.

Thank you so much for all your help and time.

Sincerely

Dan

You could cycle through the SPID's in master.dbo.sysprocess and KILL off any SPID's using the test database_id.

And then you have learned why you 'should' not 'play' on a production server. Create yourself a local server for experiementation and testing. Get an 'old', decommissioned desktop and convert it to a test server. There is no excuse for mucking up a production server.

Client Problems

Hello:

I have an instance of SQL Express in my Server with the necesary configurations(enabling TCP/IP connections).

The problem is: with .udl files, I can create a connections very well in other machines, but, I my Win application can′t connect to the server. My connections string is:

"Data Source=192.168.1.106\SQLEXPRESS;Initial Catalog=Products;User ID=sa;Password=sqlsa"

I installed SQL Native Client in the machines where my application is executed (sqlncli.msi).

Thanks

Do you have SQL Browser running on the remote computer and an Exception created in the remote computers firewall? SQL Browser is required to enumerate the named instances on a server. Check out the FAQ at the top of this forum for a pointer to instructions about remote connections.

Mike

|||

Yes I have the SQL Browser running. But, the problem is in the clients. Could be a SQL Native Client?

|||

Hi,

I got the same problem.

I think this is a taff problem when connecting to remote comp from .net application

I use:

Data Source=MYREMOTESVR\SQLEXPRESS;Initial Catalog=microDB;Integrated Security=SSPI;Persist Security Info=True;User ID=micro;Password=micro

OR

Data Source=MYREMOTESVR\SQLEXPRESS;Initial Catalog=microdb;Integrated Security=True;Persist Security Info=True;User ID=micro;Password=micro

And found EROR message when running aplication from client comp.

"... Login failed foruser MYCOMP\Guest... "

Anyone can help us?

regards.

md5

|||

hi,

MD5 wrote:

Data Source=MYREMOTESVR\SQLEXPRESS;Initial Catalog=microdb;Integrated Security=True;Persist Security Info=True;User ID=micro;Password=micro

And found EROR message when running aplication from client comp.

"... Login failed foruser MYCOMP\Guest... "

actually you pass to much info to your connection string.. the connection can be "trusted" or "not", but not both.. in this case, as you pass both, SQLExpress uses integrated security, and the "guest" (Windows OS based) login is (fortunately) not granted access to the SQLExpress instance..

so you have to choose waht you want..

integrated security (Integrated Security=True

OR

standard SQL Server authentication (User ID=micro;Password=micro

?

regards

Client Problems

Hello:

I have an instance of SQL Express in my Server with the necesary configurations(enabling TCP/IP connections).

The problem is: with .udl files, I can create a connections very well in other machines, but, I my Win application can′t connect to the server. My connections string is:

"Data Source=192.168.1.106\SQLEXPRESS;Initial Catalog=Products;User ID=sa;Password=sqlsa"

I installed SQL Native Client in the machines where my application is executed (sqlncli.msi).

Thanks

Do you have SQL Browser running on the remote computer and an Exception created in the remote computers firewall? SQL Browser is required to enumerate the named instances on a server. Check out the FAQ at the top of this forum for a pointer to instructions about remote connections.

Mike

|||

Yes I have the SQL Browser running. But, the problem is in the clients. Could be a SQL Native Client?

|||

Hi,

I got the same problem.

I think this is a taff problem when connecting to remote comp from .net application

I use:

Data Source=MYREMOTESVR\SQLEXPRESS;Initial Catalog=microDB;Integrated Security=SSPI;Persist Security Info=True;User ID=micro;Password=micro

OR

Data Source=MYREMOTESVR\SQLEXPRESS;Initial Catalog=microdb;Integrated Security=True;Persist Security Info=True;User ID=micro;Password=micro

And found EROR message when running aplication from client comp.

"... Login failed foruser MYCOMP\Guest... "

Anyone can help us?

regards.

md5

|||

hi,

MD5 wrote:

Data Source=MYREMOTESVR\SQLEXPRESS;Initial Catalog=microdb;Integrated Security=True;Persist Security Info=True;User ID=micro;Password=micro

And found EROR message when running aplication from client comp.

"... Login failed foruser MYCOMP\Guest... "

actually you pass to much info to your connection string.. the connection can be "trusted" or "not", but not both.. in this case, as you pass both, SQLExpress uses integrated security, and the "guest" (Windows OS based) login is (fortunately) not granted access to the SQLExpress instance..

so you have to choose waht you want..

integrated security (Integrated Security=True

OR

standard SQL Server authentication (User ID=micro;Password=micro

?

regards

Client Problems

Hello:

I have an instance of SQL Express in my Server with the necesary configurations(enabling TCP/IP connections).

The problem is: with .udl files, I can create a connections very well in other machines, but, I my Win application can′t connect to the server. My connections string is:

"Data Source=192.168.1.106\SQLEXPRESS;Initial Catalog=Products;User ID=sa;Password=sqlsa"

I installed SQL Native Client in the machines where my application is executed (sqlncli.msi).

Thanks

Do you have SQL Browser running on the remote computer and an Exception created in the remote computers firewall? SQL Browser is required to enumerate the named instances on a server. Check out the FAQ at the top of this forum for a pointer to instructions about remote connections.

Mike

|||

Yes I have the SQL Browser running. But, the problem is in the clients. Could be a SQL Native Client?

|||

Hi,

I got the same problem.

I think this is a taff problem when connecting to remote comp from .net application

I use:

Data Source=MYREMOTESVR\SQLEXPRESS;Initial Catalog=microDB;Integrated Security=SSPI;Persist Security Info=True;User ID=micro;Password=micro

OR

Data Source=MYREMOTESVR\SQLEXPRESS;Initial Catalog=microdb;Integrated Security=True;Persist Security Info=True;User ID=micro;Password=micro

And found EROR message when running aplication from client comp.

"... Login failed foruser MYCOMP\Guest... "

Anyone can help us?

regards.

md5

|||

hi,

MD5 wrote:

Data Source=MYREMOTESVR\SQLEXPRESS;Initial Catalog=microdb;Integrated Security=True;Persist Security Info=True;User ID=micro;Password=micro

And found EROR message when running aplication from client comp.

"... Login failed foruser MYCOMP\Guest... "

actually you pass to much info to your connection string.. the connection can be "trusted" or "not", but not both.. in this case, as you pass both, SQLExpress uses integrated security, and the "guest" (Windows OS based) login is (fortunately) not granted access to the SQLExpress instance..

so you have to choose waht you want..

integrated security (Integrated Security=True

OR

standard SQL Server authentication (User ID=micro;Password=micro

?

regards

Client License Question - SQL and Server

If I'm running a Legato Co-Standby or CA Brightstor failover setup, where
only one server is accepting connections at a time.
Do I need to purchase a full count of SQL and Server client licenses for
both servers.
I've heard that I can have for example; a fifty client and a zero client box
but set them both to 50.
MS site doesn't specificly cover this
http://www.microsoft.com/sql/howtobuy/faq.mspx
I could say it's similar to an active/pasive cluster because the secon
server is strictly passive.
<snip from ms site>
. Active/passive. In the active/passive configuration, one or more
computers in the cluster do not regularly process information but rather
passively wait to pick up the workload when an active server fails. All
active servers in a cluster must be fully licensed, according to either the
Per Processor licensing model or the Server/CAL licensing model. However, if
a server is strictly passive, working only while an active server has
failed, no additional licenses are needed for that passive server. The only
exception to this rule is if the cluster is licensed using Processor
licenses and the number of processors on the passive server exceeds the
number of processors on the active server. In these cases, additional
Processor licenses must be purchased for the additional processors on the
passive server.
thnks,
SherpaSherpa,
This sounds like an active/passive configuration in which the licensing from
the below snipit would apply. Having said that, I would probably follow up
with Microsoft directly on the licensing question and get an answer in email
for future reference if needed.
HTH
Jerry
"Sherpa" <1@.1.com> wrote in message
news:%23vIfPh7vFHA.3000@.TK2MSFTNGP12.phx.gbl...
> If I'm running a Legato Co-Standby or CA Brightstor failover setup, where
> only one server is accepting connections at a time.
> Do I need to purchase a full count of SQL and Server client licenses for
> both servers.
> I've heard that I can have for example; a fifty client and a zero client
> box
> but set them both to 50.
>
> MS site doesn't specificly cover this
> http://www.microsoft.com/sql/howtobuy/faq.mspx
> I could say it's similar to an active/pasive cluster because the secon
> server is strictly passive.
> <snip from ms site>
> . Active/passive. In the active/passive configuration, one or more
> computers in the cluster do not regularly process information but rather
> passively wait to pick up the workload when an active server fails. All
> active servers in a cluster must be fully licensed, according to either
> the
> Per Processor licensing model or the Server/CAL licensing model. However,
> if
> a server is strictly passive, working only while an active server has
> failed, no additional licenses are needed for that passive server. The
> only
> exception to this rule is if the cluster is licensed using Processor
> licenses and the number of processors on the passive server exceeds the
> number of processors on the active server. In these cases, additional
> Processor licenses must be purchased for the additional processors on the
> passive server.
>
>
> thnks,
> Sherpa
>|||If you are using server+CAL licensing then you just need a server licence
for each server plus a CAL for each client. You don't need to purchase
double the number of CALs.
David Portas
SQL Server MVP
--|||Hello,
Since the question is a license issue, you may call the licensing team
directly at 1-800-426-9400 (select option 4), Monday through Friday, 6:00
A.M. to 5:30 P.M. (PST) to speak directly to a Microsoft licensing
specialist in the United States and Canada.
Worldwide customers can use the Guide to Worldwide Microsoft Licensing
Sites <http://www.microsoft.com/licensing/index/worldwide.asp>
to find contact information in their locations.
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
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.

Client License Question - SQL and Server

If I'm running a Legato Co-Standby or CA Brightstor failover setup, where
only one server is accepting connections at a time.
Do I need to purchase a full count of SQL and Server client licenses for
both servers.
I've heard that I can have for example; a fifty client and a zero client box
but set them both to 50.
MS site doesn't specificly cover this
http://www.microsoft.com/sql/howtobuy/faq.mspx
I could say it's similar to an active/pasive cluster because the secon
server is strictly passive.
<snip from ms site>
. Active/passive. In the active/passive configuration, one or more
computers in the cluster do not regularly process information but rather
passively wait to pick up the workload when an active server fails. All
active servers in a cluster must be fully licensed, according to either the
Per Processor licensing model or the Server/CAL licensing model. However, if
a server is strictly passive, working only while an active server has
failed, no additional licenses are needed for that passive server. The only
exception to this rule is if the cluster is licensed using Processor
licenses and the number of processors on the passive server exceeds the
number of processors on the active server. In these cases, additional
Processor licenses must be purchased for the additional processors on the
passive server.
thnks,
Sherpa
Sherpa,
This sounds like an active/passive configuration in which the licensing from
the below snipit would apply. Having said that, I would probably follow up
with Microsoft directly on the licensing question and get an answer in email
for future reference if needed.
HTH
Jerry
"Sherpa" <1@.1.com> wrote in message
news:%23vIfPh7vFHA.3000@.TK2MSFTNGP12.phx.gbl...
> If I'm running a Legato Co-Standby or CA Brightstor failover setup, where
> only one server is accepting connections at a time.
> Do I need to purchase a full count of SQL and Server client licenses for
> both servers.
> I've heard that I can have for example; a fifty client and a zero client
> box
> but set them both to 50.
>
> MS site doesn't specificly cover this
> http://www.microsoft.com/sql/howtobuy/faq.mspx
> I could say it's similar to an active/pasive cluster because the secon
> server is strictly passive.
> <snip from ms site>
> . Active/passive. In the active/passive configuration, one or more
> computers in the cluster do not regularly process information but rather
> passively wait to pick up the workload when an active server fails. All
> active servers in a cluster must be fully licensed, according to either
> the
> Per Processor licensing model or the Server/CAL licensing model. However,
> if
> a server is strictly passive, working only while an active server has
> failed, no additional licenses are needed for that passive server. The
> only
> exception to this rule is if the cluster is licensed using Processor
> licenses and the number of processors on the passive server exceeds the
> number of processors on the active server. In these cases, additional
> Processor licenses must be purchased for the additional processors on the
> passive server.
>
>
> thnks,
> Sherpa
>
|||If you are using server+CAL licensing then you just need a server licence
for each server plus a CAL for each client. You don't need to purchase
double the number of CALs.
David Portas
SQL Server MVP
|||Hello,
Since the question is a license issue, you may call the licensing team
directly at 1-800-426-9400 (select option 4), Monday through Friday, 6:00
A.M. to 5:30 P.M. (PST) to speak directly to a Microsoft licensing
specialist in the United States and Canada.
Worldwide customers can use the Guide to Worldwide Microsoft Licensing
Sites <http://www.microsoft.com/licensing/index/worldwide.asp>
to find contact information in their locations.
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
================================================== ===
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.

Client License Question - SQL and Server

If I'm running a Legato Co-Standby or CA Brightstor failover setup, where
only one server is accepting connections at a time.
Do I need to purchase a full count of SQL and Server client licenses for
both servers.
I've heard that I can have for example; a fifty client and a zero client box
but set them both to 50.
thnks,
Sherpa
Hello,
I notice that you have posted the same question in our SQLServer newsgroup,
which I have already responded. Please check the answers there and if you
need any further assistance on this particular issue, please reply in that
thread. Thanks for your understanding and cooperation.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
================================================== ===
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.

Client License Question - SQL and Server

If I'm running a Legato Co-Standby or CA Brightstor failover setup, where
only one server is accepting connections at a time.
Do I need to purchase a full count of SQL and Server client licenses for
both servers.
I've heard that I can have for example; a fifty client and a zero client box
but set them both to 50.
MS site doesn't specificly cover this
http://www.microsoft.com/sql/howtobuy/faq.mspx
I could say it's similar to an active/pasive cluster because the secon
server is strictly passive.
<snip from ms site>
. Active/passive. In the active/passive configuration, one or more
computers in the cluster do not regularly process information but rather
passively wait to pick up the workload when an active server fails. All
active servers in a cluster must be fully licensed, according to either the
Per Processor licensing model or the Server/CAL licensing model. However, if
a server is strictly passive, working only while an active server has
failed, no additional licenses are needed for that passive server. The only
exception to this rule is if the cluster is licensed using Processor
licenses and the number of processors on the passive server exceeds the
number of processors on the active server. In these cases, additional
Processor licenses must be purchased for the additional processors on the
passive server.
thnks,
SherpaSherpa,
This sounds like an active/passive configuration in which the licensing from
the below snipit would apply. Having said that, I would probably follow up
with Microsoft directly on the licensing question and get an answer in email
for future reference if needed.
HTH
Jerry
"Sherpa" <1@.1.com> wrote in message
news:%23vIfPh7vFHA.3000@.TK2MSFTNGP12.phx.gbl...
> If I'm running a Legato Co-Standby or CA Brightstor failover setup, where
> only one server is accepting connections at a time.
> Do I need to purchase a full count of SQL and Server client licenses for
> both servers.
> I've heard that I can have for example; a fifty client and a zero client
> box
> but set them both to 50.
>
> MS site doesn't specificly cover this
> http://www.microsoft.com/sql/howtobuy/faq.mspx
> I could say it's similar to an active/pasive cluster because the secon
> server is strictly passive.
> <snip from ms site>
> . Active/passive. In the active/passive configuration, one or more
> computers in the cluster do not regularly process information but rather
> passively wait to pick up the workload when an active server fails. All
> active servers in a cluster must be fully licensed, according to either
> the
> Per Processor licensing model or the Server/CAL licensing model. However,
> if
> a server is strictly passive, working only while an active server has
> failed, no additional licenses are needed for that passive server. The
> only
> exception to this rule is if the cluster is licensed using Processor
> licenses and the number of processors on the passive server exceeds the
> number of processors on the active server. In these cases, additional
> Processor licenses must be purchased for the additional processors on the
> passive server.
>
>
> thnks,
> Sherpa
>|||If you are using server+CAL licensing then you just need a server licence
for each server plus a CAL for each client. You don't need to purchase
double the number of CALs.
--
David Portas
SQL Server MVP
--|||Hello,
Since the question is a license issue, you may call the licensing team
directly at 1-800-426-9400 (select option 4), Monday through Friday, 6:00
A.M. to 5:30 P.M. (PST) to speak directly to a Microsoft licensing
specialist in the United States and Canada.
Worldwide customers can use the Guide to Worldwide Microsoft Licensing
Sites <http://www.microsoft.com/licensing/index/worldwide.asp>
to find contact information in their locations.
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
=====================================================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.

Client cannot access SQL 2005 Express Server

I've installed SQL 2005 Express, enabled local and Remote connections but my client app cannot connect to the SQL server. I'm getting access denied or SQL does not exists. Any idea's on how to resolve this issue?

Perhaps these articles will help:

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

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

Configuration -Connect to SQL Express and ‘Stay Connected’
http://betav.com/blog/billva/2006/06/getting_and_staying_connected.html

Clearing Single User Mode/lock

What is the best way to clear Single User mode from SQL Server 2000?

I have tried going to all tasks and selecting detach and clear Connections using this database. I then click cancel and go into the properties and options and clear the Restrict access check box. This seems to work some times but not all the times. I think it is because something else connects to it to it while I am going through the above steps. As a last resort I have detached the database and re-attach it.

What is causing the single user mode to happen in the first place?

I am a little confused, the system is going into single user mode of its own accord and you want to get it back out again? Have you looked at the error log to find out why it is going into Single User?

This will set a database into single user and then back out again;

ALTER DATABASE AdventureWorks
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO

ALTER DATABASE AdventureWorks
SET MULTI_USER;
GO

|||

The error log has the following

DBCC CHECKDB (ANS, repair_fast) executed by zz101zz found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 9 seconds.

I don't see any other entrys or errors.

|||
Does script I gave you bring it out?|||I will try it next time it goes into single user mode. Thanks!|||

One of my coworkers tried the script and batch file but said that he got a message that it couldn't be done when database is already open.

ALTER DATABASE ANS
SET MULTI_USER;
GO

echo off
Echo *** This bat will attempt to Clear Single User Mode on ANS Database ***
echo *** This hasn't been tested yet ***
echo *** If you are unsure CTRL C will get you out ***
Pause
osql -S Server -U -P -i ClearSingleUserModeOn-ANS_Database.sql
Pause

|||Please post the exact error message|||

Exact Message is

Msg 5064, Level 16, State 1, Server OHP03028, Line 1
Changes to the state or options of database 'ANS' cannot be made at this time.
The database is in single-user mode, and a user is currently connected to it.
Msg 5069, Level 16, State 1, Server OHP03028, Line 1
ALTER DATABASE statement failed.

|||So, you should find this user's spid and kill his connection by KILL command, and then you will be able to change the database state.|||

I'm having the same problem... and tried to kill any connection to the database from the activity monitor... but did not find any connection to it...

Clearing Single User Mode/lock

What is the best way to clear Single User mode from SQL Server 2000?

I have tried going to all tasks and selecting detach and clear Connections using this database. I then click cancel and go into the properties and options and clear the Restrict access check box. This seems to work some times but not all the times. I think it is because something else connects to it to it while I am going through the above steps. As a last resort I have detached the database and re-attach it.

What is causing the single user mode to happen in the first place?

I am a little confused, the system is going into single user mode of its own accord and you want to get it back out again? Have you looked at the error log to find out why it is going into Single User?

This will set a database into single user and then back out again;

ALTER DATABASE AdventureWorks
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO

ALTER DATABASE AdventureWorks
SET MULTI_USER;
GO

|||

The error log has the following

DBCC CHECKDB (ANS, repair_fast) executed by zz101zz found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 9 seconds.

I don't see any other entrys or errors.

|||
Does script I gave you bring it out?|||I will try it next time it goes into single user mode. Thanks!|||

One of my coworkers tried the script and batch file but said that he got a message that it couldn't be done when database is already open.

ALTER DATABASE ANS
SET MULTI_USER;
GO

echo off
Echo *** This bat will attempt to Clear Single User Mode on ANS Database ***
echo *** This hasn't been tested yet ***
echo *** If you are unsure CTRL C will get you out ***
Pause
osql -S Server -U -P -i ClearSingleUserModeOn-ANS_Database.sql
Pause

|||Please post the exact error message|||

Exact Message is

Msg 5064, Level 16, State 1, Server OHP03028, Line 1
Changes to the state or options of database 'ANS' cannot be made at this time.
The database is in single-user mode, and a user is currently connected to it.
Msg 5069, Level 16, State 1, Server OHP03028, Line 1
ALTER DATABASE statement failed.

|||So, you should find this user's spid and kill his connection by KILL command, and then you will be able to change the database state.|||

I'm having the same problem... and tried to kill any connection to the database from the activity monitor... but did not find any connection to it...

Clearing Connections

I use the below code to detach my database. Sometimes it fails because there
is an active connection. Is there any command that can overide that
protection or clear the connection without me having to manually detach
inside Ent. Manager?
CODE **********
use master
go
sp_detach_db 'mySQLdatabase'
goHave a look at
http://msdn2.microsoft.com/en-us/library/ms188031(SQL.90).aspx. There's a
section on Obtaining Exclusive Access that tells you how to use ALTER
DATABASE to accomplish what you're trying to do.
Sincerely,
Steve Dybing
This posting is provided "AS IS" with no warranties, and confers no rights.
"scott" <sbailey@.mileslumber.com> wrote in message
news:uoQm8bgdGHA.1276@.TK2MSFTNGP03.phx.gbl...
>I use the below code to detach my database. Sometimes it fails because
>there is an active connection. Is there any command that can overide that
>protection or clear the connection without me having to manually detach
>inside Ent. Manager?
> CODE **********
> use master
> go
> sp_detach_db 'mySQLdatabase'
> go
>