Hi ,
At first i was trying to set up replication where i got the Error 18483:
could not connect to server 'SQL1; because 'distributor_admin' is not defined
as a remote login at the server.
as per some of the posting i used "Select @.@.Servername" (e.g SQL2)and found
that the name returned is not the same as the computer name(e.g SQL1)
so i executed sp_dropserver @.@.Servername (SQl2)
now when i tried to configure it as a publisher , it gives me "could not
configure SQL1 as the distributor for SQL1. error 18482 : could not connect
to SQL1 because 'SQL2' is not defined as a remote server
i have tried sp_addserver SQL1 but it does not work either
kindly advise
tks & rdgs
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...ation/200512/1
Hi ,
After i stop & start SQL Server , i got error 14114 : null is not
configured as a distributor and null is returned for select @.@.Servername
kindly advise
tks & rdgs
maxzsim wrote:
>Hi ,
> At first i was trying to set up replication where i got the Error 18483:
>could not connect to server 'SQL1; because 'distributor_admin' is not defined
>as a remote login at the server.
>as per some of the posting i used "Select @.@.Servername" (e.g SQL2)and found
>that the name returned is not the same as the computer name(e.g SQL1)
>so i executed sp_dropserver @.@.Servername (SQl2)
>now when i tried to configure it as a publisher , it gives me "could not
>configure SQL1 as the distributor for SQL1. error 18482 : could not connect
>to SQL1 because 'SQL2' is not defined as a remote server
>i have tried sp_addserver SQL1 but it does not work either
>kindly advise
>tks & rdgs
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...ation/200512/1
|||Hi,
The steps below from one of the posting works
sp_dropserver 'wrong server name'
sp_addserver 'correct server name'
stop & start sql services( i forgot to do this step earlier)
tks & rdgs
maxzsim wrote:[vbcol=seagreen]
>Hi ,
> After i stop & start SQL Server , i got error 14114 : null is not
>configured as a distributor and null is returned for select @.@.Servername
>kindly advise
>tks & rdgs
>[quoted text clipped - 16 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...ation/200512/1
|||Had similar problem with starting the replication distributor.
Got login failed message for distributor_admin user.
Problem: Local SQL Server instance name incorrect
We changed the name of the server a while back but the @.@.Servername had the
old server name.
Solution:
sp_dropserver 'oldservername'
sp_addserver 'newservername', @.local = 'local'
The @.local ='local' is required because the replication server is the local
server not a remote server.
Also you need to stop and restart SQL Server Agent.
"maxzsim via droptable.com" wrote:
> Hi,
> The steps below from one of the posting works
> sp_dropserver 'wrong server name'
> sp_addserver 'correct server name'
> stop & start sql services( i forgot to do this step earlier)
> tks & rdgs
>
> maxzsim wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...ation/200512/1
>
Showing posts with label connect. Show all posts
Showing posts with label connect. Show all posts
Thursday, March 29, 2012
error 18456 when trying to connect to 2005 using odbc
Hi all
I am using Windows XP SP2 with a Sql server 2005 Express installation. I created a test db with a user called test, schema also test. I gave the permission connect, db_datareader and db_datawriter. I can now connect into this user using WinSQL. But if I want to connect using a application which is using ODBC, then I get the error:
[SQLSTATE=28000 - [Microsoft][SQL Native][SQL Server]Login failed for user 'test'.[Native Error=18456]]
Because I connect using ODBC, I think there must be the problem. But I have to connect with ODBC. There is no other possibility.
any ideas ?
Regards, Waff
I found this out:
The server collation is defined to: Latin1_General_CI_AS
If I create a new login id in lowercase, then ODBC will change it to uppercase and then the server refuse the access. But why. CI means case insensitive, so doesn't matter if upper or lower case. should.
How do you use a id in lowercase and connecto via ODBC ?
You need to enable TCP in order for ODBC clients to connect. Check out http://blogs.msdn.com/sqlexpress/archive/2004/07/23/192044.aspx for information about doing this.
Mike
|||Guess has something to do with the collation. I used Latin1_General_AI_CI.
If I create my login id in lowercase and try then to connect to the db using ODBC, then ODBC will switch my id to uppercase and so I can't logon.
Anyway, somehow, it must be possible to modify the server settings, so that it doesn't matter how I logon. lowercase or uppercase. I tryed with CI and CS but still them same.
ODBC is enabled but think has to do with the collation.
What are you using if you use id's as upper/lower case as Test ?
error 18456 when trying to connect to 2005 using odbc
Hi all
I am using Windows XP SP2 with a Sql server 2005 Express installation. I created a test db with a user called test, schema also test. I gave the permission connect, db_datareader and db_datawriter. I can now connect into this user using WinSQL. But if I want to connect using a application which is using ODBC, then I get the error:
[SQLSTATE=28000 - [Microsoft][SQL Native][SQL Server]Login failed for user 'test'.[Native Error=18456]]
Because I connect using ODBC, I think there must be the problem. But I have to connect with ODBC. There is no other possibility.
any ideas ?
Regards, Waff
I found this out:
The server collation is defined to: Latin1_General_CI_AS
If I create a new login id in lowercase, then ODBC will change it to uppercase and then the server refuse the access. But why. CI means case insensitive, so doesn't matter if upper or lower case. should.
How do you use a id in lowercase and connecto via ODBC ?
You need to enable TCP in order for ODBC clients to connect. Check out http://blogs.msdn.com/sqlexpress/archive/2004/07/23/192044.aspx for information about doing this.
Mike
|||Guess has something to do with the collation. I used Latin1_General_AI_CI.
If I create my login id in lowercase and try then to connect to the db using ODBC, then ODBC will switch my id to uppercase and so I can't logon.
Anyway, somehow, it must be possible to modify the server settings, so that it doesn't matter how I logon. lowercase or uppercase. I tryed with CI and CS but still them same.
ODBC is enabled but think has to do with the collation.
What are you using if you use id's as upper/lower case as Test ?
Error 18456 State 6
Hi,
I am trying to connect to a remote SQL server via Management Studio. I am using the windows credentials I use to log into the computer but when I enter the same domain\username and password I get error 18456. I check the logs and see it is a state 6 error. After checking http://blogs.msdn.com/sql_protocols/archive/2006/02/21/536201.aspx I see that means
Attempt to use a Windows login name with SQL Authentication
Is it possible for me to access a SQL server remotely?
Thanks
From your description, I would assume that you chose “SQL Server Authentication” and provided a user name that was a Windows user name, correct? “SQL Server Authentication” is used only for principals whose information is completely under the control of the SQL Server instance (i.e. the SID, login and password are only valid to SQL Server).
When using Windows authentication the server will use the Windows token from your client program (in this case Management Studio) to authenticate the connection, without the need to type any login or password.
When you connect to SQL Server using Management Studio, make sure you choose the option “Windows Authentication” for the “Authentication” value, the user name and password fields should be grayed out.
I hope this helps, but let us know if you still have any questions.
-Raul Garcia
SDE/T
SQL Server Engine
sqlError 18456 Severity: 14 State 11 problem
I am installing diskeeper administrator on one server with at database on our sql2005 server. When I am trying to connect to the database the diskeeper software says verify username and password. I try to connect using windows authentication .And in the serverlog this error occurs
2006-11-27 15:04:56.01 Logon Login failed for user 'NB\Servername$'. [CLIENT: "Ipadress"]
2006-11-27 15:04:56.01 Logon Error: 18456, Severity: 14, State: 11.
When i try to connect with Sql server authentication, this error occurs in the log:
2006-11-27 15:48:35.46 Logon Login failed for user 'NB\SERVERNAME$'. [CLIENT: ipadress
]2006-11-27 16:04:22.94 Logon Error: 18456, Severity: 14, State: 16.
I have tried to disable filesharing, and run installation of diskeepper as the domain administrator. But the same error occurs.
Can anyone help me with this
Regards Oyvind
State 11 indicates an error determining the database to be used after authentication. This could happen if the database cannot be accessed for some reason. You could try to connect to the master database initially, to see if that works, then change context to the database that you normally use and see what error you encounter, if any.
However, I am not familiar with the diskeeper software and I don't understand its relation with SQL Server, so I'm not sure how to help you further.
Thanks
Laurentiu
Diskeeper do not have any link with SQL Server in this case, and also it is no use in running for linked server related error.
What is the privilege for the account used on the linked server?
Refer to KBA http://support.microsoft.com/kb/889646 fyi.
|||The privilege for the account is sysadmin.
The default database is master.
I also tried to connect with odbc, but the same error occurs
Regards
Oyvind
|||have you contacted the vendor in this case?|||Correction to my previous post: the state discussion applies to state 16, not to state 11.
State 11 indicates a different problem.
What is strange is that, while you mention using Windows and SQL authentication, both connections appear to be attempted via Windows authentication, using the machine account credentials.
Best thing would be to check with the diskeeper customer support.
Error 18456 - Connecting via DSN
Hi,
My MS Access application needs to connect to a SQL server via a system
dsn. However, I keep getting the error 18456 - Login failed for user 'xxxx'.
The surprising bit is I am able to use the same password and user id and
connect to the server using Enterprise Manager. Any ideas on why the dsn is
throwing this error? Connection is via SQL authentication.
Any help would be much appreciated.
Rgds,
San
Any success with this? I'm having the exact same problem. I was doing
some permissions stuff the other day so I'm sure I hosed something up.
goosesr
Posted via http://www.codecomments.com
|||Yeah, I was able to resolve the issue by updating the links in Access. Right
click on the link table, select Link Table Manager, select, promt for new
location,a nd on ok, select the DSN again...
Hope it works for you too !
~San
"goosesr" wrote:
> Any success with this? I'm having the exact same problem. I was doing
> some permissions stuff the other day so I'm sure I hosed something up.
>
> --
> goosesr
> Posted via http://www.codecomments.com
>
My MS Access application needs to connect to a SQL server via a system
dsn. However, I keep getting the error 18456 - Login failed for user 'xxxx'.
The surprising bit is I am able to use the same password and user id and
connect to the server using Enterprise Manager. Any ideas on why the dsn is
throwing this error? Connection is via SQL authentication.
Any help would be much appreciated.
Rgds,
San
Any success with this? I'm having the exact same problem. I was doing
some permissions stuff the other day so I'm sure I hosed something up.
goosesr
Posted via http://www.codecomments.com
|||Yeah, I was able to resolve the issue by updating the links in Access. Right
click on the link table, select Link Table Manager, select, promt for new
location,a nd on ok, select the DSN again...
Hope it works for you too !
~San
"goosesr" wrote:
> Any success with this? I'm having the exact same problem. I was doing
> some permissions stuff the other day so I'm sure I hosed something up.
>
> --
> goosesr
> Posted via http://www.codecomments.com
>
Error 18456 - Connecting via DSN
Hi,
My MS Access application needs to connect to a SQL server via a system
dsn. However, I keep getting the error 18456 - Login failed for user 'xxxx'.
The surprising bit is I am able to use the same password and user id and
connect to the server using Enterprise Manager. Any ideas on why the dsn is
throwing this error? Connection is via SQL authentication.
Any help would be much appreciated.
Rgds,
SanAny success with this? I'm having the exact same problem. I was doing
some permissions stuff the other day so I'm sure I hosed something up.
goosesr
---
Posted via http://www.codecomments.com
---|||Yeah, I was able to resolve the issue by updating the links in Access. Right
click on the link table, select Link Table Manager, select, promt for new
location,a nd on ok, select the DSN again...
Hope it works for you too !
~San
"goosesr" wrote:
> Any success with this? I'm having the exact same problem. I was doing
> some permissions stuff the other day so I'm sure I hosed something up.
>
> --
> goosesr
> ---
> Posted via http://www.codecomments.com
> ---
>
My MS Access application needs to connect to a SQL server via a system
dsn. However, I keep getting the error 18456 - Login failed for user 'xxxx'.
The surprising bit is I am able to use the same password and user id and
connect to the server using Enterprise Manager. Any ideas on why the dsn is
throwing this error? Connection is via SQL authentication.
Any help would be much appreciated.
Rgds,
SanAny success with this? I'm having the exact same problem. I was doing
some permissions stuff the other day so I'm sure I hosed something up.
goosesr
---
Posted via http://www.codecomments.com
---|||Yeah, I was able to resolve the issue by updating the links in Access. Right
click on the link table, select Link Table Manager, select, promt for new
location,a nd on ok, select the DSN again...
Hope it works for you too !
~San
"goosesr" wrote:
> Any success with this? I'm having the exact same problem. I was doing
> some permissions stuff the other day so I'm sure I hosed something up.
>
> --
> goosesr
> ---
> Posted via http://www.codecomments.com
> ---
>
Tuesday, March 27, 2012
Error 18452
Hi,
If I connect to SQLServer Express via Windows Authentication it works fine. But if I use SQL Server Authentication it doesn't work with a new created user and sa (with a new password) too. I turned mixed mode on, registry key HKLM\Software\Microsoft\Microsoft Sql Server\MSSQL.1\\MSSQLServer\login has the value 2 and all ports are set to 1433 but there is still error 18452. Any suggestions?
|||I guess the system you configured and the system you are connecting to are tw different ones then.|||No, it's the same system and I have only this one in the moment.
|||
Thank you.
sql
If I connect to SQLServer Express via Windows Authentication it works fine. But if I use SQL Server Authentication it doesn't work with a new created user and sa (with a new password) too. I turned mixed mode on, registry key HKLM\Software\Microsoft\Microsoft Sql Server\MSSQL.1\\MSSQLServer\login has the value 2 and all ports are set to 1433 but there is still error 18452. Any suggestions?
Did you restart the server service ?
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
|||I guess the system you configured and the system you are connecting to are tw different ones then.|||No, it's the same system and I have only this one in the moment.
|||
What is the exact error message that you are getting back? Take a look at the errorlog as well to see what message is logged there.
How are you trying to connect? If you're connecting through an application, make sure that you don't specify integrated authentication in the connection string.
Thanks
Laurentiu
Thank you.
sql
Error 18452
I have a co-worker who installed SQL Server 2000 on her
machine. For some reason, when she tries to connect to
Query Analyzer using SQL Server authentication she gets
error 18452 (Login failed for user 'sa'. Reason: Not
associated with a trusted SQL Server connection.).
I don't know exactly how she installed SQL Server 2000,
but the only way for her to connect right now is by
windows authentication, which is what's recommended.
Anyways, I would like to know how can one resolve this
issue.
Thanks in advance,
BdeeTo change the Authentication mode for the server via Enterprise Manager,
Right click on the Server and choose Properties. Goto the security Tab in
the dialog and select the SQL Server and Windows radio button in the
Authentication section and click ok. You will need to restart the SQL
service for this to take effect
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"bd" <anonymous@.discussions.microsoft.com> wrote in message
news:d6d301c439f9$0a6de270$a301280a@.phx.gbl...
> I have a co-worker who installed SQL Server 2000 on her
> machine. For some reason, when she tries to connect to
> Query Analyzer using SQL Server authentication she gets
> error 18452 (Login failed for user 'sa'. Reason: Not
> associated with a trusted SQL Server connection.).
> I don't know exactly how she installed SQL Server 2000,
> but the only way for her to connect right now is by
> windows authentication, which is what's recommended.
> Anyways, I would like to know how can one resolve this
> issue.
> Thanks in advance,
> Bdee
machine. For some reason, when she tries to connect to
Query Analyzer using SQL Server authentication she gets
error 18452 (Login failed for user 'sa'. Reason: Not
associated with a trusted SQL Server connection.).
I don't know exactly how she installed SQL Server 2000,
but the only way for her to connect right now is by
windows authentication, which is what's recommended.
Anyways, I would like to know how can one resolve this
issue.
Thanks in advance,
BdeeTo change the Authentication mode for the server via Enterprise Manager,
Right click on the Server and choose Properties. Goto the security Tab in
the dialog and select the SQL Server and Windows radio button in the
Authentication section and click ok. You will need to restart the SQL
service for this to take effect
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"bd" <anonymous@.discussions.microsoft.com> wrote in message
news:d6d301c439f9$0a6de270$a301280a@.phx.gbl...
> I have a co-worker who installed SQL Server 2000 on her
> machine. For some reason, when she tries to connect to
> Query Analyzer using SQL Server authentication she gets
> error 18452 (Login failed for user 'sa'. Reason: Not
> associated with a trusted SQL Server connection.).
> I don't know exactly how she installed SQL Server 2000,
> but the only way for her to connect right now is by
> windows authentication, which is what's recommended.
> Anyways, I would like to know how can one resolve this
> issue.
> Thanks in advance,
> Bdee
Error 18452
I have a co-worker who installed SQL Server 2000 on her
machine. For some reason, when she tries to connect to
Query Analyzer using SQL Server authentication she gets
error 18452 (Login failed for user 'sa'. Reason: Not
associated with a trusted SQL Server connection.).
I don't know exactly how she installed SQL Server 2000,
but the only way for her to connect right now is by
windows authentication, which is what's recommended.
Anyways, I would like to know how can one resolve this
issue.
Thanks in advance,
BdeeTo change the Authentication mode for the server via Enterprise Manager,
Right click on the Server and choose Properties. Goto the security Tab in
the dialog and select the SQL Server and Windows radio button in the
Authentication section and click ok. You will need to restart the SQL
service for this to take effect
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"bd" <anonymous@.discussions.microsoft.com> wrote in message
news:d6d301c439f9$0a6de270$a301280a@.phx.gbl...
> I have a co-worker who installed SQL Server 2000 on her
> machine. For some reason, when she tries to connect to
> Query Analyzer using SQL Server authentication she gets
> error 18452 (Login failed for user 'sa'. Reason: Not
> associated with a trusted SQL Server connection.).
> I don't know exactly how she installed SQL Server 2000,
> but the only way for her to connect right now is by
> windows authentication, which is what's recommended.
> Anyways, I would like to know how can one resolve this
> issue.
> Thanks in advance,
> Bdee
machine. For some reason, when she tries to connect to
Query Analyzer using SQL Server authentication she gets
error 18452 (Login failed for user 'sa'. Reason: Not
associated with a trusted SQL Server connection.).
I don't know exactly how she installed SQL Server 2000,
but the only way for her to connect right now is by
windows authentication, which is what's recommended.
Anyways, I would like to know how can one resolve this
issue.
Thanks in advance,
BdeeTo change the Authentication mode for the server via Enterprise Manager,
Right click on the Server and choose Properties. Goto the security Tab in
the dialog and select the SQL Server and Windows radio button in the
Authentication section and click ok. You will need to restart the SQL
service for this to take effect
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"bd" <anonymous@.discussions.microsoft.com> wrote in message
news:d6d301c439f9$0a6de270$a301280a@.phx.gbl...
> I have a co-worker who installed SQL Server 2000 on her
> machine. For some reason, when she tries to connect to
> Query Analyzer using SQL Server authentication she gets
> error 18452 (Login failed for user 'sa'. Reason: Not
> associated with a trusted SQL Server connection.).
> I don't know exactly how she installed SQL Server 2000,
> but the only way for her to connect right now is by
> windows authentication, which is what's recommended.
> Anyways, I would like to know how can one resolve this
> issue.
> Thanks in advance,
> Bdee
Error 18452
I have a co-worker who installed SQL Server 2000 on her
machine. For some reason, when she tries to connect to
Query Analyzer using SQL Server authentication she gets
error 18452 (Login failed for user 'sa'. Reason: Not
associated with a trusted SQL Server connection.).
I don't know exactly how she installed SQL Server 2000,
but the only way for her to connect right now is by
windows authentication, which is what's recommended.
Anyways, I would like to know how can one resolve this
issue.
Thanks in advance,
Bdee
To change the Authentication mode for the server via Enterprise Manager,
Right click on the Server and choose Properties. Goto the security Tab in
the dialog and select the SQL Server and Windows radio button in the
Authentication section and click ok. You will need to restart the SQL
service for this to take effect
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"bd" <anonymous@.discussions.microsoft.com> wrote in message
news:d6d301c439f9$0a6de270$a301280a@.phx.gbl...
> I have a co-worker who installed SQL Server 2000 on her
> machine. For some reason, when she tries to connect to
> Query Analyzer using SQL Server authentication she gets
> error 18452 (Login failed for user 'sa'. Reason: Not
> associated with a trusted SQL Server connection.).
> I don't know exactly how she installed SQL Server 2000,
> but the only way for her to connect right now is by
> windows authentication, which is what's recommended.
> Anyways, I would like to know how can one resolve this
> issue.
> Thanks in advance,
> Bdee
sql
machine. For some reason, when she tries to connect to
Query Analyzer using SQL Server authentication she gets
error 18452 (Login failed for user 'sa'. Reason: Not
associated with a trusted SQL Server connection.).
I don't know exactly how she installed SQL Server 2000,
but the only way for her to connect right now is by
windows authentication, which is what's recommended.
Anyways, I would like to know how can one resolve this
issue.
Thanks in advance,
Bdee
To change the Authentication mode for the server via Enterprise Manager,
Right click on the Server and choose Properties. Goto the security Tab in
the dialog and select the SQL Server and Windows radio button in the
Authentication section and click ok. You will need to restart the SQL
service for this to take effect
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"bd" <anonymous@.discussions.microsoft.com> wrote in message
news:d6d301c439f9$0a6de270$a301280a@.phx.gbl...
> I have a co-worker who installed SQL Server 2000 on her
> machine. For some reason, when she tries to connect to
> Query Analyzer using SQL Server authentication she gets
> error 18452 (Login failed for user 'sa'. Reason: Not
> associated with a trusted SQL Server connection.).
> I don't know exactly how she installed SQL Server 2000,
> but the only way for her to connect right now is by
> windows authentication, which is what's recommended.
> Anyways, I would like to know how can one resolve this
> issue.
> Thanks in advance,
> Bdee
sql
Monday, March 26, 2012
Error 17805 Invalid buffer received from Client when executing stored Procedure
Hello,
I am receiving this error when I try to execute a Stored Procedure in
SQL Server 2000 using SYBASE dblib.
I have been able to connect to connect to SQL Server from Unix, but
when I try to execute a Stored Procedure via dblib, I get the error:
"Error 17805 Invalid buffer received from Client"
My Stored Procedure does not receive parameters, so I don't know what
is happening.
If I execute a simple SQL Statement, it works fine
I have a SQL Server 2000 running SP 4.
anyone can help?
Thank you in advanced
Jose Manuel
Jose Manuel wrote:
> Hello,
> I am receiving this error when I try to execute a Stored Procedure in
> SQL Server 2000 using SYBASE dblib.
> I have been able to connect to connect to SQL Server from Unix, but
> when I try to execute a Stored Procedure via dblib, I get the error:
> "Error 17805 Invalid buffer received from Client"
> My Stored Procedure does not receive parameters, so I don't know what
> is happening.
> If I execute a simple SQL Statement, it works fine
> I have a SQL Server 2000 running SP 4.
> anyone can help?
> Thank you in advanced
> Jose Manuel
Just curious: Why are you executing SQL on SQL Server 2000 usin the
Sybase dbLib instead of a SQL Server client library?
David Gugick
Imceda Software
www.imceda.com
|||Jose Manuel,
I am having exactlly the same problem as you had.
Could you please tell me how did you solve it?
Thanks
creo que voy varios pasos atras de ti. ojal me puedas ayudar. tengo el
mismo problema.
te ha contactado alguien ?
lo pudiste resolver?
gracias.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
I am receiving this error when I try to execute a Stored Procedure in
SQL Server 2000 using SYBASE dblib.
I have been able to connect to connect to SQL Server from Unix, but
when I try to execute a Stored Procedure via dblib, I get the error:
"Error 17805 Invalid buffer received from Client"
My Stored Procedure does not receive parameters, so I don't know what
is happening.
If I execute a simple SQL Statement, it works fine
I have a SQL Server 2000 running SP 4.
anyone can help?
Thank you in advanced
Jose Manuel
Jose Manuel wrote:
> Hello,
> I am receiving this error when I try to execute a Stored Procedure in
> SQL Server 2000 using SYBASE dblib.
> I have been able to connect to connect to SQL Server from Unix, but
> when I try to execute a Stored Procedure via dblib, I get the error:
> "Error 17805 Invalid buffer received from Client"
> My Stored Procedure does not receive parameters, so I don't know what
> is happening.
> If I execute a simple SQL Statement, it works fine
> I have a SQL Server 2000 running SP 4.
> anyone can help?
> Thank you in advanced
> Jose Manuel
Just curious: Why are you executing SQL on SQL Server 2000 usin the
Sybase dbLib instead of a SQL Server client library?
David Gugick
Imceda Software
www.imceda.com
|||Jose Manuel,
I am having exactlly the same problem as you had.
Could you please tell me how did you solve it?
Thanks
creo que voy varios pasos atras de ti. ojal me puedas ayudar. tengo el
mismo problema.
te ha contactado alguien ?
lo pudiste resolver?
gracias.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Error 17803, Severity: 20, State: 12
This is an annoying one.
I have 4 test boxes running our application. The application is a wervice
using ADO.Net to connect to SQL Server. The machines are all P4, 2.8GHZ CPU.
Windows Server 2003 Std. Edition, w/ all current patches (as of 10/13/04)
running SQL Server 2000 Developer Edition. SP3a. The machines are also
runnign Share Point Services and BIz Talk 2004, although neither of those are
doing anything at this time. Two of the machines have 1GB physical memory, 2
have 2GB.
We have one test case where have to process a 50MB message (SOAP post with
50MB payload). The problem, interestingly enough occurs on both of the
machines with 2GB. With a default install of SQL Server, configured to
dynamically allocate memory, the insert saving the 50MB message fails. SQL
Server logs "Error: 17803, Severity 20, State:12 Insufficient memory
available." Monitoring physical memory it doesn't appear that the box is
even close to out of memory. If I change the SQL server configuration to use
a fixed or minimum amount of memory (1GB) the insert works 8 out of 9 times,
but it still occasionally fails logging the same error. It makes no sense,
especially where it works 100% of the time on the two boxes with less memory.
Any help here would be appreciated.
Hi GGould,
The answer to your question lies in the the way SQL allocates memory.
Every process (on a 32bits machine) gets and 4 GB Virtual address space in
this address space 2 GB is devote to user, 2 GB for kernel allocations.
SQL server, when it starts with the default memory configuration will map
his executable and dlls into this 2 GB virtual user address space and will
allocate all physical memory in the system to it's Bpool data "cache" of 8K
buffers, leaving 256 MB of his virtual address space for his so called mem to
leave which ican be used for all memory allocations > 8 KB.
On a system with 2 GB SQL will allocate ~1.7 GB physical memory for its
bpool and .exe/dlls. Mapping all this into the virtual address space exactlu
leaves ~256 mb for the mem to leave area
On a s system with 1 GB, SQL will allocate ~.7 GB physical mmeory for bpool
and .exe/dlls. Mapping this memory into the virtual address space leaves
~1.256 mb for allocations ending up in the mem to leave area.
As for your action it's required to allocate a >8kb block SQL will allocate
such block and map it in to the memto leave area.
If you need a memory block > can fit in mem to leave area you get the
insufficient memory error.
To workaround on the 2 GB system you can use the -gxxx startup parametera nd
specify > 256 for xxx . At startup SQL will leave more space for the mem to
leave area at the cost of Bpool
Ab othe options is if you have a enterprice edition of the OS to use the
/3GB. take care however wjen using terminal server and make sure the machine
is a dedicated SQL server else don't use /3gb in the boot.ini.
Regards,
Jago
> This is an annoying one.
> I have 4 test boxes running our application. The application is a wervice
> using ADO.Net to connect to SQL Server. The machines are all P4, 2.8GHZ CPU.
> Windows Server 2003 Std. Edition, w/ all current patches (as of 10/13/04)
> running SQL Server 2000 Developer Edition. SP3a. The machines are also
> runnign Share Point Services and BIz Talk 2004, although neither of those are
> doing anything at this time. Two of the machines have 1GB physical memory, 2
> have 2GB.
> We have one test case where have to process a 50MB message (SOAP post with
> 50MB payload). The problem, interestingly enough occurs on both of the
> machines with 2GB. With a default install of SQL Server, configured to
> dynamically allocate memory, the insert saving the 50MB message fails. SQL
> Server logs "Error: 17803, Severity 20, State:12 Insufficient memory
> available." Monitoring physical memory it doesn't appear that the box is
> even close to out of memory. If I change the SQL server configuration to use
> a fixed or minimum amount of memory (1GB) the insert works 8 out of 9 times,
> but it still occasionally fails logging the same error. It makes no sense,
> especially where it works 100% of the time on the two boxes with less memory.
> Any help here would be appreciated.
|||Thanks,
That seems to have taken care of it.
"Jago" wrote:
[vbcol=seagreen]
> Hi GGould,
> The answer to your question lies in the the way SQL allocates memory.
> Every process (on a 32bits machine) gets and 4 GB Virtual address space in
> this address space 2 GB is devote to user, 2 GB for kernel allocations.
> SQL server, when it starts with the default memory configuration will map
> his executable and dlls into this 2 GB virtual user address space and will
> allocate all physical memory in the system to it's Bpool data "cache" of 8K
> buffers, leaving 256 MB of his virtual address space for his so called mem to
> leave which ican be used for all memory allocations > 8 KB.
> On a system with 2 GB SQL will allocate ~1.7 GB physical memory for its
> bpool and .exe/dlls. Mapping all this into the virtual address space exactlu
> leaves ~256 mb for the mem to leave area
> On a s system with 1 GB, SQL will allocate ~.7 GB physical mmeory for bpool
> and .exe/dlls. Mapping this memory into the virtual address space leaves
> ~1.256 mb for allocations ending up in the mem to leave area.
> As for your action it's required to allocate a >8kb block SQL will allocate
> such block and map it in to the memto leave area.
> If you need a memory block > can fit in mem to leave area you get the
> insufficient memory error.
> To workaround on the 2 GB system you can use the -gxxx startup parametera nd
> specify > 256 for xxx . At startup SQL will leave more space for the mem to
> leave area at the cost of Bpool
> Ab othe options is if you have a enterprice edition of the OS to use the
> /3GB. take care however wjen using terminal server and make sure the machine
> is a dedicated SQL server else don't use /3gb in the boot.ini.
>
> Regards,
>
> Jago
sql
I have 4 test boxes running our application. The application is a wervice
using ADO.Net to connect to SQL Server. The machines are all P4, 2.8GHZ CPU.
Windows Server 2003 Std. Edition, w/ all current patches (as of 10/13/04)
running SQL Server 2000 Developer Edition. SP3a. The machines are also
runnign Share Point Services and BIz Talk 2004, although neither of those are
doing anything at this time. Two of the machines have 1GB physical memory, 2
have 2GB.
We have one test case where have to process a 50MB message (SOAP post with
50MB payload). The problem, interestingly enough occurs on both of the
machines with 2GB. With a default install of SQL Server, configured to
dynamically allocate memory, the insert saving the 50MB message fails. SQL
Server logs "Error: 17803, Severity 20, State:12 Insufficient memory
available." Monitoring physical memory it doesn't appear that the box is
even close to out of memory. If I change the SQL server configuration to use
a fixed or minimum amount of memory (1GB) the insert works 8 out of 9 times,
but it still occasionally fails logging the same error. It makes no sense,
especially where it works 100% of the time on the two boxes with less memory.
Any help here would be appreciated.
Hi GGould,
The answer to your question lies in the the way SQL allocates memory.
Every process (on a 32bits machine) gets and 4 GB Virtual address space in
this address space 2 GB is devote to user, 2 GB for kernel allocations.
SQL server, when it starts with the default memory configuration will map
his executable and dlls into this 2 GB virtual user address space and will
allocate all physical memory in the system to it's Bpool data "cache" of 8K
buffers, leaving 256 MB of his virtual address space for his so called mem to
leave which ican be used for all memory allocations > 8 KB.
On a system with 2 GB SQL will allocate ~1.7 GB physical memory for its
bpool and .exe/dlls. Mapping all this into the virtual address space exactlu
leaves ~256 mb for the mem to leave area
On a s system with 1 GB, SQL will allocate ~.7 GB physical mmeory for bpool
and .exe/dlls. Mapping this memory into the virtual address space leaves
~1.256 mb for allocations ending up in the mem to leave area.
As for your action it's required to allocate a >8kb block SQL will allocate
such block and map it in to the memto leave area.
If you need a memory block > can fit in mem to leave area you get the
insufficient memory error.
To workaround on the 2 GB system you can use the -gxxx startup parametera nd
specify > 256 for xxx . At startup SQL will leave more space for the mem to
leave area at the cost of Bpool
Ab othe options is if you have a enterprice edition of the OS to use the
/3GB. take care however wjen using terminal server and make sure the machine
is a dedicated SQL server else don't use /3gb in the boot.ini.
Regards,
Jago
> This is an annoying one.
> I have 4 test boxes running our application. The application is a wervice
> using ADO.Net to connect to SQL Server. The machines are all P4, 2.8GHZ CPU.
> Windows Server 2003 Std. Edition, w/ all current patches (as of 10/13/04)
> running SQL Server 2000 Developer Edition. SP3a. The machines are also
> runnign Share Point Services and BIz Talk 2004, although neither of those are
> doing anything at this time. Two of the machines have 1GB physical memory, 2
> have 2GB.
> We have one test case where have to process a 50MB message (SOAP post with
> 50MB payload). The problem, interestingly enough occurs on both of the
> machines with 2GB. With a default install of SQL Server, configured to
> dynamically allocate memory, the insert saving the 50MB message fails. SQL
> Server logs "Error: 17803, Severity 20, State:12 Insufficient memory
> available." Monitoring physical memory it doesn't appear that the box is
> even close to out of memory. If I change the SQL server configuration to use
> a fixed or minimum amount of memory (1GB) the insert works 8 out of 9 times,
> but it still occasionally fails logging the same error. It makes no sense,
> especially where it works 100% of the time on the two boxes with less memory.
> Any help here would be appreciated.
|||Thanks,
That seems to have taken care of it.
"Jago" wrote:
[vbcol=seagreen]
> Hi GGould,
> The answer to your question lies in the the way SQL allocates memory.
> Every process (on a 32bits machine) gets and 4 GB Virtual address space in
> this address space 2 GB is devote to user, 2 GB for kernel allocations.
> SQL server, when it starts with the default memory configuration will map
> his executable and dlls into this 2 GB virtual user address space and will
> allocate all physical memory in the system to it's Bpool data "cache" of 8K
> buffers, leaving 256 MB of his virtual address space for his so called mem to
> leave which ican be used for all memory allocations > 8 KB.
> On a system with 2 GB SQL will allocate ~1.7 GB physical memory for its
> bpool and .exe/dlls. Mapping all this into the virtual address space exactlu
> leaves ~256 mb for the mem to leave area
> On a s system with 1 GB, SQL will allocate ~.7 GB physical mmeory for bpool
> and .exe/dlls. Mapping this memory into the virtual address space leaves
> ~1.256 mb for allocations ending up in the mem to leave area.
> As for your action it's required to allocate a >8kb block SQL will allocate
> such block and map it in to the memto leave area.
> If you need a memory block > can fit in mem to leave area you get the
> insufficient memory error.
> To workaround on the 2 GB system you can use the -gxxx startup parametera nd
> specify > 256 for xxx . At startup SQL will leave more space for the mem to
> leave area at the cost of Bpool
> Ab othe options is if you have a enterprice edition of the OS to use the
> /3GB. take care however wjen using terminal server and make sure the machine
> is a dedicated SQL server else don't use /3gb in the boot.ini.
>
> Regards,
>
> Jago
sql
Error 17803, Severity: 20, State: 12
This is an annoying one.
I have 4 test boxes running our application. The application is a wervice
using ADO.Net to connect to SQL Server. The machines are all P4, 2.8GHZ CPU
.
Windows Server 2003 Std. Edition, w/ all current patches (as of 10/13/04)
running SQL Server 2000 Developer Edition. SP3a. The machines are also
runnign Share Point Services and BIz Talk 2004, although neither of those ar
e
doing anything at this time. Two of the machines have 1GB physical memory,
2
have 2GB.
We have one test case where have to process a 50MB message (SOAP post with
50MB payload). The problem, interestingly enough occurs on both of the
machines with 2GB. With a default install of SQL Server, configured to
dynamically allocate memory, the insert saving the 50MB message fails. SQL
Server logs "Error: 17803, Severity 20, State:12 Insufficient memory
available." Monitoring physical memory it doesn't appear that the box is
even close to out of memory. If I change the SQL server configuration to us
e
a fixed or minimum amount of memory (1GB) the insert works 8 out of 9 times,
but it still occasionally fails logging the same error. It makes no sense,
especially where it works 100% of the time on the two boxes with less memory
.
Any help here would be appreciated.Hi GGould,
The answer to your question lies in the the way SQL allocates memory.
Every process (on a 32bits machine) gets and 4 GB Virtual address space in
this address space 2 GB is devote to user, 2 GB for kernel allocations.
SQL server, when it starts with the default memory configuration will map
his executable and dlls into this 2 GB virtual user address space and will
allocate all physical memory in the system to it's Bpool data "cache" of 8K
buffers, leaving 256 MB of his virtual address space for his so called mem t
o
leave which ican be used for all memory allocations > 8 KB.
On a system with 2 GB SQL will allocate ~1.7 GB physical memory for its
bpool and .exe/dlls. Mapping all this into the virtual address space exactlu
leaves ~256 mb for the mem to leave area
On a s system with 1 GB, SQL will allocate ~.7 GB physical mmeory for bpool
and .exe/dlls. Mapping this memory into the virtual address space leaves
~1.256 mb for allocations ending up in the mem to leave area.
As for your action it's required to allocate a >8kb block SQL will allocate
such block and map it in to the memto leave area.
If you need a memory block > can fit in mem to leave area you get the
insufficient memory error.
To workaround on the 2 GB system you can use the -gxxx startup parametera nd
specify > 256 for xxx . At startup SQL will leave more space for the mem to
leave area at the cost of Bpool
Ab othe options is if you have a enterprice edition of the OS to use the
/3GB. take care however wjen using terminal server and make sure the machine
is a dedicated SQL server else don't use /3gb in the boot.ini.
Regards,
Jago
> This is an annoying one.
> I have 4 test boxes running our application. The application is a wervice
> using ADO.Net to connect to SQL Server. The machines are all P4, 2.8GHZ C
PU.
> Windows Server 2003 Std. Edition, w/ all current patches (as of 10/13/04)
> running SQL Server 2000 Developer Edition. SP3a. The machines are also
> runnign Share Point Services and BIz Talk 2004, although neither of those
are
> doing anything at this time. Two of the machines have 1GB physical memory
, 2
> have 2GB.
> We have one test case where have to process a 50MB message (SOAP post with
> 50MB payload). The problem, interestingly enough occurs on both of the
> machines with 2GB. With a default install of SQL Server, configured to
> dynamically allocate memory, the insert saving the 50MB message fails. SQ
L
> Server logs "Error: 17803, Severity 20, State:12 Insufficient memory
> available." Monitoring physical memory it doesn't appear that the box is
> even close to out of memory. If I change the SQL server configuration to
use
> a fixed or minimum amount of memory (1GB) the insert works 8 out of 9 time
s,
> but it still occasionally fails logging the same error. It makes no sense
,
> especially where it works 100% of the time on the two boxes with less memo
ry.
> Any help here would be appreciated.|||Thanks,
That seems to have taken care of it.
"Jago" wrote:
[vbcol=seagreen]
> Hi GGould,
> The answer to your question lies in the the way SQL allocates memory.
> Every process (on a 32bits machine) gets and 4 GB Virtual address space in
> this address space 2 GB is devote to user, 2 GB for kernel allocations.
> SQL server, when it starts with the default memory configuration will map
> his executable and dlls into this 2 GB virtual user address space and will
> allocate all physical memory in the system to it's Bpool data "cache" of 8
K
> buffers, leaving 256 MB of his virtual address space for his so called mem
to
> leave which ican be used for all memory allocations > 8 KB.
> On a system with 2 GB SQL will allocate ~1.7 GB physical memory for its
> bpool and .exe/dlls. Mapping all this into the virtual address space exact
lu
> leaves ~256 mb for the mem to leave area
> On a s system with 1 GB, SQL will allocate ~.7 GB physical mmeory for bpoo
l
> and .exe/dlls. Mapping this memory into the virtual address space leaves
> ~1.256 mb for allocations ending up in the mem to leave area.
> As for your action it's required to allocate a >8kb block SQL will allocat
e
> such block and map it in to the memto leave area.
> If you need a memory block > can fit in mem to leave area you get the
> insufficient memory error.
> To workaround on the 2 GB system you can use the -gxxx startup parametera
nd
> specify > 256 for xxx . At startup SQL will leave more space for the mem t
o
> leave area at the cost of Bpool
> Ab othe options is if you have a enterprice edition of the OS to use the
> /3GB. take care however wjen using terminal server and make sure the machi
ne
> is a dedicated SQL server else don't use /3gb in the boot.ini.
>
> Regards,
>
> Jago
I have 4 test boxes running our application. The application is a wervice
using ADO.Net to connect to SQL Server. The machines are all P4, 2.8GHZ CPU
.
Windows Server 2003 Std. Edition, w/ all current patches (as of 10/13/04)
running SQL Server 2000 Developer Edition. SP3a. The machines are also
runnign Share Point Services and BIz Talk 2004, although neither of those ar
e
doing anything at this time. Two of the machines have 1GB physical memory,
2
have 2GB.
We have one test case where have to process a 50MB message (SOAP post with
50MB payload). The problem, interestingly enough occurs on both of the
machines with 2GB. With a default install of SQL Server, configured to
dynamically allocate memory, the insert saving the 50MB message fails. SQL
Server logs "Error: 17803, Severity 20, State:12 Insufficient memory
available." Monitoring physical memory it doesn't appear that the box is
even close to out of memory. If I change the SQL server configuration to us
e
a fixed or minimum amount of memory (1GB) the insert works 8 out of 9 times,
but it still occasionally fails logging the same error. It makes no sense,
especially where it works 100% of the time on the two boxes with less memory
.
Any help here would be appreciated.Hi GGould,
The answer to your question lies in the the way SQL allocates memory.
Every process (on a 32bits machine) gets and 4 GB Virtual address space in
this address space 2 GB is devote to user, 2 GB for kernel allocations.
SQL server, when it starts with the default memory configuration will map
his executable and dlls into this 2 GB virtual user address space and will
allocate all physical memory in the system to it's Bpool data "cache" of 8K
buffers, leaving 256 MB of his virtual address space for his so called mem t
o
leave which ican be used for all memory allocations > 8 KB.
On a system with 2 GB SQL will allocate ~1.7 GB physical memory for its
bpool and .exe/dlls. Mapping all this into the virtual address space exactlu
leaves ~256 mb for the mem to leave area
On a s system with 1 GB, SQL will allocate ~.7 GB physical mmeory for bpool
and .exe/dlls. Mapping this memory into the virtual address space leaves
~1.256 mb for allocations ending up in the mem to leave area.
As for your action it's required to allocate a >8kb block SQL will allocate
such block and map it in to the memto leave area.
If you need a memory block > can fit in mem to leave area you get the
insufficient memory error.
To workaround on the 2 GB system you can use the -gxxx startup parametera nd
specify > 256 for xxx . At startup SQL will leave more space for the mem to
leave area at the cost of Bpool
Ab othe options is if you have a enterprice edition of the OS to use the
/3GB. take care however wjen using terminal server and make sure the machine
is a dedicated SQL server else don't use /3gb in the boot.ini.
Regards,
Jago
> This is an annoying one.
> I have 4 test boxes running our application. The application is a wervice
> using ADO.Net to connect to SQL Server. The machines are all P4, 2.8GHZ C
PU.
> Windows Server 2003 Std. Edition, w/ all current patches (as of 10/13/04)
> running SQL Server 2000 Developer Edition. SP3a. The machines are also
> runnign Share Point Services and BIz Talk 2004, although neither of those
are
> doing anything at this time. Two of the machines have 1GB physical memory
, 2
> have 2GB.
> We have one test case where have to process a 50MB message (SOAP post with
> 50MB payload). The problem, interestingly enough occurs on both of the
> machines with 2GB. With a default install of SQL Server, configured to
> dynamically allocate memory, the insert saving the 50MB message fails. SQ
L
> Server logs "Error: 17803, Severity 20, State:12 Insufficient memory
> available." Monitoring physical memory it doesn't appear that the box is
> even close to out of memory. If I change the SQL server configuration to
use
> a fixed or minimum amount of memory (1GB) the insert works 8 out of 9 time
s,
> but it still occasionally fails logging the same error. It makes no sense
,
> especially where it works 100% of the time on the two boxes with less memo
ry.
> Any help here would be appreciated.|||Thanks,
That seems to have taken care of it.
"Jago" wrote:
[vbcol=seagreen]
> Hi GGould,
> The answer to your question lies in the the way SQL allocates memory.
> Every process (on a 32bits machine) gets and 4 GB Virtual address space in
> this address space 2 GB is devote to user, 2 GB for kernel allocations.
> SQL server, when it starts with the default memory configuration will map
> his executable and dlls into this 2 GB virtual user address space and will
> allocate all physical memory in the system to it's Bpool data "cache" of 8
K
> buffers, leaving 256 MB of his virtual address space for his so called mem
to
> leave which ican be used for all memory allocations > 8 KB.
> On a system with 2 GB SQL will allocate ~1.7 GB physical memory for its
> bpool and .exe/dlls. Mapping all this into the virtual address space exact
lu
> leaves ~256 mb for the mem to leave area
> On a s system with 1 GB, SQL will allocate ~.7 GB physical mmeory for bpoo
l
> and .exe/dlls. Mapping this memory into the virtual address space leaves
> ~1.256 mb for allocations ending up in the mem to leave area.
> As for your action it's required to allocate a >8kb block SQL will allocat
e
> such block and map it in to the memto leave area.
> If you need a memory block > can fit in mem to leave area you get the
> insufficient memory error.
> To workaround on the 2 GB system you can use the -gxxx startup parametera
nd
> specify > 256 for xxx . At startup SQL will leave more space for the mem t
o
> leave area at the cost of Bpool
> Ab othe options is if you have a enterprice edition of the OS to use the
> /3GB. take care however wjen using terminal server and make sure the machi
ne
> is a dedicated SQL server else don't use /3gb in the boot.ini.
>
> Regards,
>
> Jago
Error 17803, Severity: 20, State: 12
This is an annoying one.
I have 4 test boxes running our application. The application is a wervice
using ADO.Net to connect to SQL Server. The machines are all P4, 2.8GHZ CPU.
Windows Server 2003 Std. Edition, w/ all current patches (as of 10/13/04)
running SQL Server 2000 Developer Edition. SP3a. The machines are also
runnign Share Point Services and BIz Talk 2004, although neither of those are
doing anything at this time. Two of the machines have 1GB physical memory, 2
have 2GB.
We have one test case where have to process a 50MB message (SOAP post with
50MB payload). The problem, interestingly enough occurs on both of the
machines with 2GB. With a default install of SQL Server, configured to
dynamically allocate memory, the insert saving the 50MB message fails. SQL
Server logs "Error: 17803, Severity 20, State:12 Insufficient memory
available." Monitoring physical memory it doesn't appear that the box is
even close to out of memory. If I change the SQL server configuration to use
a fixed or minimum amount of memory (1GB) the insert works 8 out of 9 times,
but it still occasionally fails logging the same error. It makes no sense,
especially where it works 100% of the time on the two boxes with less memory.
Any help here would be appreciated.Hi GGould,
The answer to your question lies in the the way SQL allocates memory.
Every process (on a 32bits machine) gets and 4 GB Virtual address space in
this address space 2 GB is devote to user, 2 GB for kernel allocations.
SQL server, when it starts with the default memory configuration will map
his executable and dlls into this 2 GB virtual user address space and will
allocate all physical memory in the system to it's Bpool data "cache" of 8K
buffers, leaving 256 MB of his virtual address space for his so called mem to
leave which ican be used for all memory allocations > 8 KB.
On a system with 2 GB SQL will allocate ~1.7 GB physical memory for its
bpool and .exe/dlls. Mapping all this into the virtual address space exactlu
leaves ~256 mb for the mem to leave area
On a s system with 1 GB, SQL will allocate ~.7 GB physical mmeory for bpool
and .exe/dlls. Mapping this memory into the virtual address space leaves
~1.256 mb for allocations ending up in the mem to leave area.
As for your action it's required to allocate a >8kb block SQL will allocate
such block and map it in to the memto leave area.
If you need a memory block > can fit in mem to leave area you get the
insufficient memory error.
To workaround on the 2 GB system you can use the -gxxx startup parametera nd
specify > 256 for xxx . At startup SQL will leave more space for the mem to
leave area at the cost of Bpool
Ab othe options is if you have a enterprice edition of the OS to use the
/3GB. take care however wjen using terminal server and make sure the machine
is a dedicated SQL server else don't use /3gb in the boot.ini.
Regards,
Jago
> This is an annoying one.
> I have 4 test boxes running our application. The application is a wervice
> using ADO.Net to connect to SQL Server. The machines are all P4, 2.8GHZ CPU.
> Windows Server 2003 Std. Edition, w/ all current patches (as of 10/13/04)
> running SQL Server 2000 Developer Edition. SP3a. The machines are also
> runnign Share Point Services and BIz Talk 2004, although neither of those are
> doing anything at this time. Two of the machines have 1GB physical memory, 2
> have 2GB.
> We have one test case where have to process a 50MB message (SOAP post with
> 50MB payload). The problem, interestingly enough occurs on both of the
> machines with 2GB. With a default install of SQL Server, configured to
> dynamically allocate memory, the insert saving the 50MB message fails. SQL
> Server logs "Error: 17803, Severity 20, State:12 Insufficient memory
> available." Monitoring physical memory it doesn't appear that the box is
> even close to out of memory. If I change the SQL server configuration to use
> a fixed or minimum amount of memory (1GB) the insert works 8 out of 9 times,
> but it still occasionally fails logging the same error. It makes no sense,
> especially where it works 100% of the time on the two boxes with less memory.
> Any help here would be appreciated.|||Thanks,
That seems to have taken care of it.
"Jago" wrote:
> Hi GGould,
> The answer to your question lies in the the way SQL allocates memory.
> Every process (on a 32bits machine) gets and 4 GB Virtual address space in
> this address space 2 GB is devote to user, 2 GB for kernel allocations.
> SQL server, when it starts with the default memory configuration will map
> his executable and dlls into this 2 GB virtual user address space and will
> allocate all physical memory in the system to it's Bpool data "cache" of 8K
> buffers, leaving 256 MB of his virtual address space for his so called mem to
> leave which ican be used for all memory allocations > 8 KB.
> On a system with 2 GB SQL will allocate ~1.7 GB physical memory for its
> bpool and .exe/dlls. Mapping all this into the virtual address space exactlu
> leaves ~256 mb for the mem to leave area
> On a s system with 1 GB, SQL will allocate ~.7 GB physical mmeory for bpool
> and .exe/dlls. Mapping this memory into the virtual address space leaves
> ~1.256 mb for allocations ending up in the mem to leave area.
> As for your action it's required to allocate a >8kb block SQL will allocate
> such block and map it in to the memto leave area.
> If you need a memory block > can fit in mem to leave area you get the
> insufficient memory error.
> To workaround on the 2 GB system you can use the -gxxx startup parametera nd
> specify > 256 for xxx . At startup SQL will leave more space for the mem to
> leave area at the cost of Bpool
> Ab othe options is if you have a enterprice edition of the OS to use the
> /3GB. take care however wjen using terminal server and make sure the machine
> is a dedicated SQL server else don't use /3gb in the boot.ini.
>
> Regards,
>
> Jago
> > This is an annoying one.
> > I have 4 test boxes running our application. The application is a wervice
> > using ADO.Net to connect to SQL Server. The machines are all P4, 2.8GHZ CPU.
> > Windows Server 2003 Std. Edition, w/ all current patches (as of 10/13/04)
> > running SQL Server 2000 Developer Edition. SP3a. The machines are also
> > runnign Share Point Services and BIz Talk 2004, although neither of those are
> > doing anything at this time. Two of the machines have 1GB physical memory, 2
> > have 2GB.
> > We have one test case where have to process a 50MB message (SOAP post with
> > 50MB payload). The problem, interestingly enough occurs on both of the
> > machines with 2GB. With a default install of SQL Server, configured to
> > dynamically allocate memory, the insert saving the 50MB message fails. SQL
> > Server logs "Error: 17803, Severity 20, State:12 Insufficient memory
> > available." Monitoring physical memory it doesn't appear that the box is
> > even close to out of memory. If I change the SQL server configuration to use
> > a fixed or minimum amount of memory (1GB) the insert works 8 out of 9 times,
> > but it still occasionally fails logging the same error. It makes no sense,
> > especially where it works 100% of the time on the two boxes with less memory.
> > Any help here would be appreciated.
I have 4 test boxes running our application. The application is a wervice
using ADO.Net to connect to SQL Server. The machines are all P4, 2.8GHZ CPU.
Windows Server 2003 Std. Edition, w/ all current patches (as of 10/13/04)
running SQL Server 2000 Developer Edition. SP3a. The machines are also
runnign Share Point Services and BIz Talk 2004, although neither of those are
doing anything at this time. Two of the machines have 1GB physical memory, 2
have 2GB.
We have one test case where have to process a 50MB message (SOAP post with
50MB payload). The problem, interestingly enough occurs on both of the
machines with 2GB. With a default install of SQL Server, configured to
dynamically allocate memory, the insert saving the 50MB message fails. SQL
Server logs "Error: 17803, Severity 20, State:12 Insufficient memory
available." Monitoring physical memory it doesn't appear that the box is
even close to out of memory. If I change the SQL server configuration to use
a fixed or minimum amount of memory (1GB) the insert works 8 out of 9 times,
but it still occasionally fails logging the same error. It makes no sense,
especially where it works 100% of the time on the two boxes with less memory.
Any help here would be appreciated.Hi GGould,
The answer to your question lies in the the way SQL allocates memory.
Every process (on a 32bits machine) gets and 4 GB Virtual address space in
this address space 2 GB is devote to user, 2 GB for kernel allocations.
SQL server, when it starts with the default memory configuration will map
his executable and dlls into this 2 GB virtual user address space and will
allocate all physical memory in the system to it's Bpool data "cache" of 8K
buffers, leaving 256 MB of his virtual address space for his so called mem to
leave which ican be used for all memory allocations > 8 KB.
On a system with 2 GB SQL will allocate ~1.7 GB physical memory for its
bpool and .exe/dlls. Mapping all this into the virtual address space exactlu
leaves ~256 mb for the mem to leave area
On a s system with 1 GB, SQL will allocate ~.7 GB physical mmeory for bpool
and .exe/dlls. Mapping this memory into the virtual address space leaves
~1.256 mb for allocations ending up in the mem to leave area.
As for your action it's required to allocate a >8kb block SQL will allocate
such block and map it in to the memto leave area.
If you need a memory block > can fit in mem to leave area you get the
insufficient memory error.
To workaround on the 2 GB system you can use the -gxxx startup parametera nd
specify > 256 for xxx . At startup SQL will leave more space for the mem to
leave area at the cost of Bpool
Ab othe options is if you have a enterprice edition of the OS to use the
/3GB. take care however wjen using terminal server and make sure the machine
is a dedicated SQL server else don't use /3gb in the boot.ini.
Regards,
Jago
> This is an annoying one.
> I have 4 test boxes running our application. The application is a wervice
> using ADO.Net to connect to SQL Server. The machines are all P4, 2.8GHZ CPU.
> Windows Server 2003 Std. Edition, w/ all current patches (as of 10/13/04)
> running SQL Server 2000 Developer Edition. SP3a. The machines are also
> runnign Share Point Services and BIz Talk 2004, although neither of those are
> doing anything at this time. Two of the machines have 1GB physical memory, 2
> have 2GB.
> We have one test case where have to process a 50MB message (SOAP post with
> 50MB payload). The problem, interestingly enough occurs on both of the
> machines with 2GB. With a default install of SQL Server, configured to
> dynamically allocate memory, the insert saving the 50MB message fails. SQL
> Server logs "Error: 17803, Severity 20, State:12 Insufficient memory
> available." Monitoring physical memory it doesn't appear that the box is
> even close to out of memory. If I change the SQL server configuration to use
> a fixed or minimum amount of memory (1GB) the insert works 8 out of 9 times,
> but it still occasionally fails logging the same error. It makes no sense,
> especially where it works 100% of the time on the two boxes with less memory.
> Any help here would be appreciated.|||Thanks,
That seems to have taken care of it.
"Jago" wrote:
> Hi GGould,
> The answer to your question lies in the the way SQL allocates memory.
> Every process (on a 32bits machine) gets and 4 GB Virtual address space in
> this address space 2 GB is devote to user, 2 GB for kernel allocations.
> SQL server, when it starts with the default memory configuration will map
> his executable and dlls into this 2 GB virtual user address space and will
> allocate all physical memory in the system to it's Bpool data "cache" of 8K
> buffers, leaving 256 MB of his virtual address space for his so called mem to
> leave which ican be used for all memory allocations > 8 KB.
> On a system with 2 GB SQL will allocate ~1.7 GB physical memory for its
> bpool and .exe/dlls. Mapping all this into the virtual address space exactlu
> leaves ~256 mb for the mem to leave area
> On a s system with 1 GB, SQL will allocate ~.7 GB physical mmeory for bpool
> and .exe/dlls. Mapping this memory into the virtual address space leaves
> ~1.256 mb for allocations ending up in the mem to leave area.
> As for your action it's required to allocate a >8kb block SQL will allocate
> such block and map it in to the memto leave area.
> If you need a memory block > can fit in mem to leave area you get the
> insufficient memory error.
> To workaround on the 2 GB system you can use the -gxxx startup parametera nd
> specify > 256 for xxx . At startup SQL will leave more space for the mem to
> leave area at the cost of Bpool
> Ab othe options is if you have a enterprice edition of the OS to use the
> /3GB. take care however wjen using terminal server and make sure the machine
> is a dedicated SQL server else don't use /3gb in the boot.ini.
>
> Regards,
>
> Jago
> > This is an annoying one.
> > I have 4 test boxes running our application. The application is a wervice
> > using ADO.Net to connect to SQL Server. The machines are all P4, 2.8GHZ CPU.
> > Windows Server 2003 Std. Edition, w/ all current patches (as of 10/13/04)
> > running SQL Server 2000 Developer Edition. SP3a. The machines are also
> > runnign Share Point Services and BIz Talk 2004, although neither of those are
> > doing anything at this time. Two of the machines have 1GB physical memory, 2
> > have 2GB.
> > We have one test case where have to process a 50MB message (SOAP post with
> > 50MB payload). The problem, interestingly enough occurs on both of the
> > machines with 2GB. With a default install of SQL Server, configured to
> > dynamically allocate memory, the insert saving the 50MB message fails. SQL
> > Server logs "Error: 17803, Severity 20, State:12 Insufficient memory
> > available." Monitoring physical memory it doesn't appear that the box is
> > even close to out of memory. If I change the SQL server configuration to use
> > a fixed or minimum amount of memory (1GB) the insert works 8 out of 9 times,
> > but it still occasionally fails logging the same error. It makes no sense,
> > especially where it works 100% of the time on the two boxes with less memory.
> > Any help here would be appreciated.
Error 17 SQL SERVER
Cannot enable TCP/IP on SQL Server 2000 when switching to a new server.
Dynamic port - Client Configuration error cannot connect.
Help
--
Regards,
JamieMore Info... DMZ server cannot be accessed from server inside DMZ. Called
for support on this one. Old DMZ was aliased in code using a name that was
different from the actual server name. Once the actual server name was used
as an alias the TCP/IP worked fine. To enable the alias, a second server
needed to be setup using an ole db alias.
--
Regards,
Jamie
"thejamie" wrote:
> Cannot enable TCP/IP on SQL Server 2000 when switching to a new server.
> Dynamic port - Client Configuration error cannot connect.
> Help
> --
> Regards,
> Jamiesql
Dynamic port - Client Configuration error cannot connect.
Help
--
Regards,
JamieMore Info... DMZ server cannot be accessed from server inside DMZ. Called
for support on this one. Old DMZ was aliased in code using a name that was
different from the actual server name. Once the actual server name was used
as an alias the TCP/IP worked fine. To enable the alias, a second server
needed to be setup using an ole db alias.
--
Regards,
Jamie
"thejamie" wrote:
> Cannot enable TCP/IP on SQL Server 2000 when switching to a new server.
> Dynamic port - Client Configuration error cannot connect.
> Help
> --
> Regards,
> Jamiesql
Thursday, March 22, 2012
error 15247 while creating subscription
Hi,
I have a local and a remote sql server that connect to remote via dial-up
connection.
In local server, I'm member of sysadmin server role but in remote server
just member of dbowner database role.
I create my publication on the local but when I want to create pull
subscription
at the subscriber(remote server) I get this error:User does not have
permission to perform this action(error 15247).
When I try to create push subscription from local server, the error is
something like: Cannot initialize the
subscription.
Any help would be greatly appreciated.
Thanks in advance.
Amin
Amin,
please check that your remote server's agent in the PAL on the publisher.
Also, that the subscriber been added to the list of potential subscribers
(sp_addsubscriber).
HTH,
Paul Ibison
|||Paul,
My local server is both publisher and distributor. All agents are running
locally. sa login is in PAL of my publication. Would you please write me
more detail.
Thanks,
Amin
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uTbm#x7VEHA.584@.TK2MSFTNGP09.phx.gbl...
> Amin,
> please check that your remote server's agent in the PAL on the publisher.
> Also, that the subscriber been added to the list of potential subscribers
> (sp_addsubscriber).
> HTH,
> Paul Ibison
>
|||Amin,
when replicating between non-trusted domains, several configuration changes
are necessary compared to the normal setup. Please have a look at this
article, which explains them: http://support.microsoft.com/?id=321822.
You'll need to set up an alias, consider pass-through security or SQL
security and the account used on subscriber must be in the PAL on the
publisher. Also you might need to edit the HOSTS file to resolve the IP
address. To test at various stages, try to use Query Analyser on the
subscriber to connect to the publisher using the subscriber agent's login.
HTH,
Paul Ibison
I have a local and a remote sql server that connect to remote via dial-up
connection.
In local server, I'm member of sysadmin server role but in remote server
just member of dbowner database role.
I create my publication on the local but when I want to create pull
subscription
at the subscriber(remote server) I get this error:User does not have
permission to perform this action(error 15247).
When I try to create push subscription from local server, the error is
something like: Cannot initialize the
subscription.
Any help would be greatly appreciated.
Thanks in advance.
Amin
Amin,
please check that your remote server's agent in the PAL on the publisher.
Also, that the subscriber been added to the list of potential subscribers
(sp_addsubscriber).
HTH,
Paul Ibison
|||Paul,
My local server is both publisher and distributor. All agents are running
locally. sa login is in PAL of my publication. Would you please write me
more detail.
Thanks,
Amin
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uTbm#x7VEHA.584@.TK2MSFTNGP09.phx.gbl...
> Amin,
> please check that your remote server's agent in the PAL on the publisher.
> Also, that the subscriber been added to the list of potential subscribers
> (sp_addsubscriber).
> HTH,
> Paul Ibison
>
|||Amin,
when replicating between non-trusted domains, several configuration changes
are necessary compared to the normal setup. Please have a look at this
article, which explains them: http://support.microsoft.com/?id=321822.
You'll need to set up an alias, consider pass-through security or SQL
security and the account used on subscriber must be in the PAL on the
publisher. Also you might need to edit the HOSTS file to resolve the IP
address. To test at various stages, try to use Query Analyser on the
subscriber to connect to the publisher using the subscriber agent's login.
HTH,
Paul Ibison
Wednesday, March 21, 2012
Error 1418, fails to connect mirror server
II use the mirroring wizard to config all these three servers(windows authorization is used), the error logs in primary server and mirror server are showed as below
a) On primary server:
Error: 1474, Severity: 16, State: 1
Database mirroring connection error 4 'An error occurred while receiving data: '10054(An existing connection was forcibly closed by the remote host.)'.' for 'TCP://mymirror.mydomain:5022'
b) On mirror server:
Database Mirroring login attempt by user 'NT AUTHORITY\ANONYMOUS LOGON.' failed with error: 'Connection handshake failed. The login 'NT AUTHORITY\ANONYMOUS LOGON' does not have CONNECT permission on the endpoint. State 84.'.
It states I do NOT have permission on this endpoint, but actually, I grant the permission to the endpoint on both primary and mirror server using command
GRANT CONNECT ON ENDPOINT::Mirroring TO [mydomain\myuser];
Also, I followed the article http://msdn2.microsoft.com/en-us/library/ms189127.aspx ,no firewall issue, the endpoint is created and started by checking sys.database_mirroring_endpoints
Hope someone can help me resolve this issue... Thank you very much..
What is the account that SQL server is running as?|||The account that SQL runs as is the one that it tries to login onto the other server as.|||Same issue here in our environment. Two database servers, one domain, one user, full permissions... Yet the connection is forcably terminated under an anonymous user.Any troubleshooting guides out there? Any how-to articles? Seems to be a lack of information regarding mirroring errors and the entire setup process... Any help would be interesting.|||
In BOL there is a section that deals explicitly with setup errors for database mirroring.
Search for "troubleshooting database mirroring."
Thanks,
Mark
|||I have exactly the same problem :
Date 5/21/2006 1:56:49 PM
Log SQL Server (Current - 5/21/2006 1:56:00 PM)
Source Logon
Message
Database Mirroring login attempt by user 'NT AUTHORITY\ANONYMOUS LOGON.' failed with error: 'Connection handshake failed. The login 'NT AUTHORITY\ANONYMOUS LOGON' does not have CONNECT permission on the endpoint. State 84.'. [CLIENT: 10.16.6.14]
That's from the error log of the mirror.
Both the principal and the mirror have sql running as the local service account for each machine respectively.
I've tried the wizard, I've tried TSQL, I've done all the troubleshooting steps you recommend. Can you please be very specific on what to do and what to put as logins when both principal and mirror run as each machines local service account.
Thanks!
Mark
|||Sorry - nevermind - it helps to read the fine print. I had not seen anywhere that if you use the local service account you must use Certificates.
Once i did that it works just fine.
Hopefully, in future releases you'll make it easier to do that.
Thanks!
Mark
|||Do you normally use Local System to run your SQL Server process?
Thanks,
Mark
|||Hello Mark,Could you send me the documentation on how to do this (create certificates), I have read a few but having problems on the mirror node.|||i too would like to create certificates i have the same problem|||
I met the same problem.
This issue is due to the wrong login authority.when we excute the TSQL "ALTER DATABASE WorkDataBase SET PARTNER ='TCP://URL:7022'",this operating lead the SQL Server send a request to the mirror point with NT AUTHORITY\ANONYMOUS enven if the SQL Server service is running as the same domain user as the mirror point does.
And when I add a new login in mirror sql server as Sysadmin with the windows account "NT AUTHORITY\ANONYMOUS",it DOES work!But that means everyone can get the full control on that machine.
if someone got other answers,please write here.
|||Hello everybody.
After 2 days experiencing problems I've finally got my first mirror working. A very basic one, but... works!
Following different sources, posts...etc I've compiled this sample guide for a Mirror Lab. Hope it can help someone in the future.
0) In this example we are inside a DOMAIN. We have a domain user called MRRUSR
1) Machine A, Machine B with Sqlserver installed. NO XP or other kind of firewalls ON.
2) MRRUSR is local admin in both machines. Also is a sysadmin user in both sqlservers.
3) MRRUSR is the MSSQLSERVER service account in both machines
4) Machine A is gonna be the PRINCIPAL, B the MIRROR.
5) Log on in my machine as MRRUSR
6) Open Sql server Studio. Connect to both machines using windows authenticacion.
7) Create a New Database in A (you may need to have sysadmin role to MRRUSR using sa) create a table, add some data
8) Backup the new DB (Full backup) with .bak extension
9) Backup the new DB (Transaction log) with .trn extension
10) Copy the both files to a location in B machine.
11) Restore .bak into a new DB using NORECOVERY option
12) Restore .trn into the previous DB using NORECOVERY option
13) Go to Machine A, open a new query
14) Take a look to the endpoints
SELECT type_desc, port FROM sys.tcp_endpoints;
SELECT name,role,state_desc FROM sys.database_mirroring_endpoints;
15) Delete existing ones (DROP ENDPOINT [NAME])
16) Create a new endopoint
CREATE ENDPOINT [Mirroring]
AS TCP (LISTENER_PORT = 5023)
FOR DATA_MIRRORING (ROLE = PARTNER, ENCRYPTION=DISABLED)
17) Start it
ALTER ENDPOINT [Mirroring]
STATE = STARTED
AS TCP (LISTENER_PORT = 5023)
FOR database_mirroring (ROLE = PARTNER);
GO
18) Do steps 13 to 17 in machine B.
19) Return to machine A. Query.
20) Lets link to our partner in machine B. First ensure you can see it with ping and telnet to the port.
ALTER DATABASE NAME
SET PARTNER ='TCP://MACHINENAME:5023'
21) Repeat step 20 from MACHINE B pointing to MACHINE A
22) Refresh the both databases in the UI, you should see the role and status of the mirror beside the database UI object.
23) Lets test them: Add some new data in Machine A database.
24) Then, right click over database, then mirror, then in the form select “FAILOVER”, then say yes.
25) Refresh the 2 databases in the UI you should see the new status and roles changed.
26) Open Machine B databse, the new data should be there.
Cheers!
Kelo
|||Another one that has shorter steps (same idea)
http://wiki.cdyne.com/index.php/Mirroring_SQL_Server_2005
|||IMPORTANT! To establish any mirroring session (with or without certificates) you must use FQDN (Fully Qualified Domain Names) machinename.domain.com.Before going on, be sure that you can reach the 3 machines with a single PING. Also, is a good practice to ensure that you can reach a telnet call over the FQDN + the port you plan to use (5022 in this example)
FIX: Over the post above. When establishing a mirroring session using machine accounts (not certificates, so you are inside a DOMAIN) ensure your 3 mssqlservices are running under NETWORK SERVICE account
|||Hi all,I'd like to add something to this however it's beyond the scope of a basic mirror. When adding a Witness to your mirror to allow for automatic failover you may experience the error on the Principle and Mirror stating
Database Mirroring login attempt by user 'Domain\SQLExpressMachineName$.' failed with error: 'Connection handshake failed. The login 'Domain\SQLExpressMachineName$.' does not have CONNECT permission on the endpoint.
This is deceiving as the Mirroring Wizard creates endpoints on all 3 hosts. Basically the error above is stating that the Witness server does now have access to the Master database endpoints on the Principle and Mirror, therefore you'll need to add the host name of the Witness Server running SQL Server Express. This is done using the GRANT tsql command
master
go
create login [Domain/SQLExpressMachineName$]
go
grant connect on endpoint::mirroring to [Domain/SQLExpressMachineName$]
I had to do this even though all SQL Services were running as a network service.
Cheers,
Damian
sql
Error 1418, fails to connect mirror server
II use the mirroring wizard to config all these three servers(windows authorization is used), the error logs in primary server and mirror server are showed as below
a) On primary server:
Error: 1474, Severity: 16, State: 1
Database mirroring connection error 4 'An error occurred while receiving data: '10054(An existing connection was forcibly closed by the remote host.)'.' for 'TCP://mymirror.mydomain:5022'
b) On mirror server:
Database Mirroring login attempt by user 'NT AUTHORITY\ANONYMOUS LOGON.' failed with error: 'Connection handshake failed. The login 'NT AUTHORITY\ANONYMOUS LOGON' does not have CONNECT permission on the endpoint. State 84.'.
It states I do NOT have permission on this endpoint, but actually, I grant the permission to the endpoint on both primary and mirror server using command
GRANT CONNECT ON ENDPOINT::Mirroring TO [mydomain\myuser];
Also, I followed the article http://msdn2.microsoft.com/en-us/library/ms189127.aspx ,no firewall issue, the endpoint is created and started by checking sys.database_mirroring_endpoints
Hope someone can help me resolve this issue... Thank you very much..
What is the account that SQL server is running as?|||The account that SQL runs as is the one that it tries to login onto the other server as.|||Same issue here in our environment. Two database servers, one domain, one user, full permissions... Yet the connection is forcably terminated under an anonymous user.Any troubleshooting guides out there? Any how-to articles? Seems to be a lack of information regarding mirroring errors and the entire setup process... Any help would be interesting.|||
In BOL there is a section that deals explicitly with setup errors for database mirroring.
Search for "troubleshooting database mirroring."
Thanks,
Mark
|||I have exactly the same problem :
Date 5/21/2006 1:56:49 PM
Log SQL Server (Current - 5/21/2006 1:56:00 PM)
Source Logon
Message
Database Mirroring login attempt by user 'NT AUTHORITY\ANONYMOUS LOGON.' failed with error: 'Connection handshake failed. The login 'NT AUTHORITY\ANONYMOUS LOGON' does not have CONNECT permission on the endpoint. State 84.'. [CLIENT: 10.16.6.14]
That's from the error log of the mirror.
Both the principal and the mirror have sql running as the local service account for each machine respectively.
I've tried the wizard, I've tried TSQL, I've done all the troubleshooting steps you recommend. Can you please be very specific on what to do and what to put as logins when both principal and mirror run as each machines local service account.
Thanks!
Mark
|||Sorry - nevermind - it helps to read the fine print. I had not seen anywhere that if you use the local service account you must use Certificates.
Once i did that it works just fine.
Hopefully, in future releases you'll make it easier to do that.
Thanks!
Mark
|||Do you normally use Local System to run your SQL Server process?
Thanks,
Mark
|||Hello Mark,Could you send me the documentation on how to do this (create certificates), I have read a few but having problems on the mirror node.|||i too would like to create certificates i have the same problem|||
I met the same problem.
This issue is due to the wrong login authority.when we excute the TSQL "ALTER DATABASE WorkDataBase SET PARTNER ='TCP://URL:7022'",this operating lead the SQL Server send a request to the mirror point with NT AUTHORITY\ANONYMOUS enven if the SQL Server service is running as the same domain user as the mirror point does.
And when I add a new login in mirror sql server as Sysadmin with the windows account "NT AUTHORITY\ANONYMOUS",it DOES work!But that means everyone can get the full control on that machine.
if someone got other answers,please write here.
|||Hello everybody.
After 2 days experiencing problems I've finally got my first mirror working. A very basic one, but... works!
Following different sources, posts...etc I've compiled this sample guide for a Mirror Lab. Hope it can help someone in the future.
0) In this example we are inside a DOMAIN. We have a domain user called MRRUSR
1) Machine A, Machine B with Sqlserver installed. NO XP or other kind of firewalls ON.
2) MRRUSR is local admin in both machines. Also is a sysadmin user in both sqlservers.
3) MRRUSR is the MSSQLSERVER service account in both machines
4) Machine A is gonna be the PRINCIPAL, B the MIRROR.
5) Log on in my machine as MRRUSR
6) Open Sql server Studio. Connect to both machines using windows authenticacion.
7) Create a New Database in A (you may need to have sysadmin role to MRRUSR using sa) create a table, add some data
8) Backup the new DB (Full backup) with .bak extension
9) Backup the new DB (Transaction log) with .trn extension
10) Copy the both files to a location in B machine.
11) Restore .bak into a new DB using NORECOVERY option
12) Restore .trn into the previous DB using NORECOVERY option
13) Go to Machine A, open a new query
14) Take a look to the endpoints
SELECT type_desc, port FROM sys.tcp_endpoints;
SELECT name,role,state_desc FROM sys.database_mirroring_endpoints;
15) Delete existing ones (DROP ENDPOINT [NAME])
16) Create a new endopoint
CREATE ENDPOINT [Mirroring]
AS TCP (LISTENER_PORT = 5023)
FOR DATA_MIRRORING (ROLE = PARTNER, ENCRYPTION=DISABLED)
17) Start it
ALTER ENDPOINT [Mirroring]
STATE = STARTED
AS TCP (LISTENER_PORT = 5023)
FOR database_mirroring (ROLE = PARTNER);
GO
18) Do steps 13 to 17 in machine B.
19) Return to machine A. Query.
20) Lets link to our partner in machine B. First ensure you can see it with ping and telnet to the port.
ALTER DATABASE NAME
SET PARTNER ='TCP://MACHINENAME:5023'
21) Repeat step 20 from MACHINE B pointing to MACHINE A
22) Refresh the both databases in the UI, you should see the role and status of the mirror beside the database UI object.
23) Lets test them: Add some new data in Machine A database.
24) Then, right click over database, then mirror, then in the form select “FAILOVER”, then say yes.
25) Refresh the 2 databases in the UI you should see the new status and roles changed.
26) Open Machine B databse, the new data should be there.
Cheers!
Kelo
|||Another one that has shorter steps (same idea)
http://wiki.cdyne.com/index.php/Mirroring_SQL_Server_2005
|||IMPORTANT! To establish any mirroring session (with or without certificates) you must use FQDN (Fully Qualified Domain Names) machinename.domain.com.Before going on, be sure that you can reach the 3 machines with a single PING. Also, is a good practice to ensure that you can reach a telnet call over the FQDN + the port you plan to use (5022 in this example)
FIX: Over the post above. When establishing a mirroring session using machine accounts (not certificates, so you are inside a DOMAIN) ensure your 3 mssqlservices are running under NETWORK SERVICE account
|||Hi all,I'd like to add something to this however it's beyond the scope of a basic mirror. When adding a Witness to your mirror to allow for automatic failover you may experience the error on the Principle and Mirror stating
Database Mirroring login attempt by user 'Domain\SQLExpressMachineName$.' failed with error: 'Connection handshake failed. The login 'Domain\SQLExpressMachineName$.' does not have CONNECT permission on the endpoint.
This is deceiving as the Mirroring Wizard creates endpoints on all 3 hosts. Basically the error above is stating that the Witness server does now have access to the Master database endpoints on the Principle and Mirror, therefore you'll need to add the host name of the Witness Server running SQL Server Express. This is done using the GRANT tsql command
master
go
create login [Domain/SQLExpressMachineName$]
go
grant connect on endpoint::mirroring to [Domain/SQLExpressMachineName$]
I had to do this even though all SQL Services were running as a network service.
Cheers,
Damian
Error 1418, fails to connect mirror server
II use the mirroring wizard to config all these three servers(windows authorization is used), the error logs in primary server and mirror server are showed as below
a) On primary server:
Error: 1474, Severity: 16, State: 1
Database mirroring connection error 4 'An error occurred while receiving data: '10054(An existing connection was forcibly closed by the remote host.)'.' for 'TCP://mymirror.mydomain:5022'
b) On mirror server:
Database Mirroring login attempt by user 'NT AUTHORITY\ANONYMOUS LOGON.' failed with error: 'Connection handshake failed. The login 'NT AUTHORITY\ANONYMOUS LOGON' does not have CONNECT permission on the endpoint. State 84.'.
It states I do NOT have permission on this endpoint, but actually, I grant the permission to the endpoint on both primary and mirror server using command
GRANT CONNECT ON ENDPOINT::Mirroring TO [mydomain\myuser];
Also, I followed the article http://msdn2.microsoft.com/en-us/library/ms189127.aspx ,no firewall issue, the endpoint is created and started by checking sys.database_mirroring_endpoints
Hope someone can help me resolve this issue... Thank you very much..
What is the account that SQL server is running as?|||The account that SQL runs as is the one that it tries to login onto the other server as.|||Same issue here in our environment. Two database servers, one domain, one user, full permissions... Yet the connection is forcably terminated under an anonymous user.Any troubleshooting guides out there? Any how-to articles? Seems to be a lack of information regarding mirroring errors and the entire setup process... Any help would be interesting.|||
In BOL there is a section that deals explicitly with setup errors for database mirroring.
Search for "troubleshooting database mirroring."
Thanks,
Mark
|||I have exactly the same problem :
Date 5/21/2006 1:56:49 PM
Log SQL Server (Current - 5/21/2006 1:56:00 PM)
Source Logon
Message
Database Mirroring login attempt by user 'NT AUTHORITY\ANONYMOUS LOGON.' failed with error: 'Connection handshake failed. The login 'NT AUTHORITY\ANONYMOUS LOGON' does not have CONNECT permission on the endpoint. State 84.'. [CLIENT: 10.16.6.14]
That's from the error log of the mirror.
Both the principal and the mirror have sql running as the local service account for each machine respectively.
I've tried the wizard, I've tried TSQL, I've done all the troubleshooting steps you recommend. Can you please be very specific on what to do and what to put as logins when both principal and mirror run as each machines local service account.
Thanks!
Mark
|||Sorry - nevermind - it helps to read the fine print. I had not seen anywhere that if you use the local service account you must use Certificates.
Once i did that it works just fine.
Hopefully, in future releases you'll make it easier to do that.
Thanks!
Mark
|||Do you normally use Local System to run your SQL Server process?
Thanks,
Mark
|||Hello Mark,Could you send me the documentation on how to do this (create certificates), I have read a few but having problems on the mirror node.|||i too would like to create certificates i have the same problem|||
I met the same problem.
This issue is due to the wrong login authority.when we excute the TSQL "ALTER DATABASE WorkDataBase SET PARTNER ='TCP://URL:7022'",this operating lead the SQL Server send a request to the mirror point with NT AUTHORITY\ANONYMOUS enven if the SQL Server service is running as the same domain user as the mirror point does.
And when I add a new login in mirror sql server as Sysadmin with the windows account "NT AUTHORITY\ANONYMOUS",it DOES work!But that means everyone can get the full control on that machine.
if someone got other answers,please write here.
|||Hello everybody.
After 2 days experiencing problems I've finally got my first mirror working. A very basic one, but... works!
Following different sources, posts...etc I've compiled this sample guide for a Mirror Lab. Hope it can help someone in the future.
0) In this example we are inside a DOMAIN. We have a domain user called MRRUSR
1) Machine A, Machine B with Sqlserver installed. NO XP or other kind of firewalls ON.
2) MRRUSR is local admin in both machines. Also is a sysadmin user in both sqlservers.
3) MRRUSR is the MSSQLSERVER service account in both machines
4) Machine A is gonna be the PRINCIPAL, B the MIRROR.
5) Log on in my machine as MRRUSR
6) Open Sql server Studio. Connect to both machines using windows authenticacion.
7) Create a New Database in A (you may need to have sysadmin role to MRRUSR using sa) create a table, add some data
8) Backup the new DB (Full backup) with .bak extension
9) Backup the new DB (Transaction log) with .trn extension
10) Copy the both files to a location in B machine.
11) Restore .bak into a new DB using NORECOVERY option
12) Restore .trn into the previous DB using NORECOVERY option
13) Go to Machine A, open a new query
14) Take a look to the endpoints
SELECT type_desc, port FROM sys.tcp_endpoints;
SELECT name,role,state_desc FROM sys.database_mirroring_endpoints;
15) Delete existing ones (DROP ENDPOINT [NAME])
16) Create a new endopoint
CREATE ENDPOINT [Mirroring]
AS TCP (LISTENER_PORT = 5023)
FOR DATA_MIRRORING (ROLE = PARTNER, ENCRYPTION=DISABLED)
17) Start it
ALTER ENDPOINT [Mirroring]
STATE = STARTED
AS TCP (LISTENER_PORT = 5023)
FOR database_mirroring (ROLE = PARTNER);
GO
18) Do steps 13 to 17 in machine B.
19) Return to machine A. Query.
20) Lets link to our partner in machine B. First ensure you can see it with ping and telnet to the port.
ALTER DATABASE NAME
SET PARTNER ='TCP://MACHINENAME:5023'
21) Repeat step 20 from MACHINE B pointing to MACHINE A
22) Refresh the both databases in the UI, you should see the role and status of the mirror beside the database UI object.
23) Lets test them: Add some new data in Machine A database.
24) Then, right click over database, then mirror, then in the form select “FAILOVER”, then say yes.
25) Refresh the 2 databases in the UI you should see the new status and roles changed.
26) Open Machine B databse, the new data should be there.
Cheers!
Kelo
|||Another one that has shorter steps (same idea)
http://wiki.cdyne.com/index.php/Mirroring_SQL_Server_2005
|||IMPORTANT! To establish any mirroring session (with or without certificates) you must use FQDN (Fully Qualified Domain Names) machinename.domain.com.Before going on, be sure that you can reach the 3 machines with a single PING. Also, is a good practice to ensure that you can reach a telnet call over the FQDN + the port you plan to use (5022 in this example)
FIX: Over the post above. When establishing a mirroring session using machine accounts (not certificates, so you are inside a DOMAIN) ensure your 3 mssqlservices are running under NETWORK SERVICE account
|||Hi all,I'd like to add something to this however it's beyond the scope of a basic mirror. When adding a Witness to your mirror to allow for automatic failover you may experience the error on the Principle and Mirror stating
Database Mirroring login attempt by user 'Domain\SQLExpressMachineName$.' failed with error: 'Connection handshake failed. The login 'Domain\SQLExpressMachineName$.' does not have CONNECT permission on the endpoint.
This is deceiving as the Mirroring Wizard creates endpoints on all 3 hosts. Basically the error above is stating that the Witness server does now have access to the Master database endpoints on the Principle and Mirror, therefore you'll need to add the host name of the Witness Server running SQL Server Express. This is done using the GRANT tsql command
master
go
create login [Domain/SQLExpressMachineName$]
go
grant connect on endpoint::mirroring to [Domain/SQLExpressMachineName$]
I had to do this even though all SQL Services were running as a network service.
Cheers,
Damian
Subscribe to:
Posts (Atom)