Greets,
I realize that this is a common issue--however, the situation seems a bit
different. Or maybe I'm naive :) Anyway, I have two servers, one hosting
the data. The other only has what it takes to run SRS.
I loaded visual studio on the SRS server for ease of use. From there, I can
create, preview, and deploy any report I like. However, even from the local
IE, I can seem to pass through the authentication. I have removed anonymous
access to the reporting website--hoping to force authentication. I have
looked at all the Web.config files, all are set for impersonation = 'true'.
What am I missing here?
Thanks!Hi Joseph,
Thank you for your posting!
My understanding of this issue is: You can not view the Report via URL and
you get the Login Failed for user '(null)' error. If I misunderstood your
concern, please feel free to let me know.
It seems that the datasource credential does not set correctly. Please try
to do the following:
1. Open Report Manager via URL.
2. Find the report and click it.
3. Click Properties, and then click Data Sources.
4. If you are using custom data source, please click the Credentials
supplied by the user running the report.
5. If you are using shared data source, then go to the datasource, and
click it. Also click the Credentials supplied by the user running the
report.
6. If the login is a Windows Credential, please check the Use as Windows
credentials when connecting to the data source.
Please let me know the result so that I can provide further assistance.
Thank you!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks for the response,
However, I do not believe that this is the issue. I am using Windows
authentication only--so as far as I can determine, no creditials can be
specified. Also, the datasource works FINE inside visual studio.
This is an IIS issue, probably having to do with one of two things:
1. The SRS Servers's computer account needs to be Trusted for Delegation
2. I need to be running the SQL services as a Domain User account...this
second one is a little suspect, because I am using the database with an
Access 2003 frontend, and all the Windows authentication works well.
Any other thoughts?
"Wei Lu" <weilu@.online.microsoft.com> wrote in message
news:JOeiC7PlGHA.2260@.TK2MSFTNGXA01.phx.gbl...
> Hi Joseph,
> Thank you for your posting!
> My understanding of this issue is: You can not view the Report via URL and
> you get the Login Failed for user '(null)' error. If I misunderstood your
> concern, please feel free to let me know.
> It seems that the datasource credential does not set correctly. Please try
> to do the following:
> 1. Open Report Manager via URL.
> 2. Find the report and click it.
> 3. Click Properties, and then click Data Sources.
> 4. If you are using custom data source, please click the Credentials
> supplied by the user running the report.
> 5. If you are using shared data source, then go to the datasource, and
> click it. Also click the Credentials supplied by the user running the
> report.
> 6. If the login is a Windows Credential, please check the Use as Windows
> credentials when connecting to the data source.
> Please let me know the result so that I can provide further assistance.
> Thank you!
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
>|||Hi Joseph,
Thank you for the information and clarification.
Let me know some information.
1. Could you access the Report Manager correctly?
2. Could you access the Report Server correctly?
3. If you could access the above site correctly, I don't think you need to
change the SQL Reporting Services start up account. Please try the method I
provided in the previous post and you could supply the Windows Credential
you use to connect to your database. ( I think it will be your domain
account or you windows login account. )
4. If you could not access the above site correctly, please let me know the
error message and so that I can provide further assistance. Thank you!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Joseph,
Have you get any chance to collect the information? Please feel free to let
me know if you have any question. Thank you!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||I've had the same problem with my reports, and my solution seems to have
been the simplest:
When you deploy a report from VS, the data source sometimes gets messed up.
I created a new data source in Report Manager and changed my report to use
this one instead. Both are using Windows Integrated Authentication. My
report didn't work with the data source generated by VS, but it worked
without any problems with the new data source.
You can still upload reports to the server, and they will use the new data
source, so you only have to do this once (at least once with every new
report).
I don't know why the data sources gets messed up. But it seems to fix my
problem. I found the clue in a news group post, so it has probably solved
the problem for someone else as well.
Kaisa M. Lindahl Lervik
"Joseph Baxter" <tanstaafl32@.newsgroups.nospam> wrote in message
news:uDTfE9IlGHA.1320@.TK2MSFTNGP04.phx.gbl...
> Greets,
> I realize that this is a common issue--however, the situation seems a bit
> different. Or maybe I'm naive :) Anyway, I have two servers, one hosting
> the data. The other only has what it takes to run SRS.
> I loaded visual studio on the SRS server for ease of use. From there, I
> can create, preview, and deploy any report I like. However, even from the
> local IE, I can seem to pass through the authentication. I have removed
> anonymous access to the reporting website--hoping to force authentication.
> I have looked at all the Web.config files, all are set for impersonation => 'true'.
> What am I missing here?
> Thanks!
>
Showing posts with label servers. Show all posts
Showing posts with label servers. Show all posts
Friday, March 23, 2012
Login failed for user '(null)'
My development environment consists of 2 separate servers (sql 2000 server
and iis6 server).
SQL 2000 Server has everything installed on it per the Microsoft
documentation.
IIS6 Web Server has everything installed on it per the Micorosoft
documentation.
My workstation has VS 2003 with reporting services install in it.
I followed an example in the Wrox SQL Server Reporting Services book.
Everything was easy to follow and managed to deploy the report to the web
server.
I tried to access the report:
http://192.168.1.4/reportserver?%2fMy+First+Report+Project%2fEmployee_List&rs:Command=Render
Got the following in return:
Reporting Services Error
----
An error has occurred during report processing. (rsProcessingAborted) Get
Online Help
Cannot create a connection to data source 'AdventureWorks2000'.
(rsErrorOpeningConnection) Get Online Help
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
----
Microsoft Reporting Services
I clicked on the links provided for help and got:
Currently there are no Microsoft Knowledge Base articles available for this
specific error or event message.
I viewed my datasourcedefinition:
--
- <DataSourceDefinition>
<Extension>SQL</Extension>
<ConnectString>data source=DEV-SRV03;initial
catalog=AdventureWorks2000</ConnectString>
<CredentialRetrieval>Integrated</CredentialRetrieval>
<Enabled>True</Enabled>
</DataSourceDefinition>
--
What do I need to do? Can someone please help me resolve this problem?
Thanks,
WillsWell here is what I did to resolve the problem.
1. Deleted the 1st attempt.
2. Create a SQL account and gave it access to the 3 dbs: AdventureWorks2000,
ReportServer and ReportServerTempDB (role: sys admin; db access with dbowner).
3. Redid the tutorial and instead of Integrated security, I went with the
sql login account.
4. Next, I had to set OverwriteDataSources to True so that it would
overwrite my existing version.
5.
http://192.168.1.4/reportserver?%2fMy+First+Report+Project%2fEmployee_List&rs:Command=Render
Gave me the report I was looking for.
Now my question is on the sql login account. What permissions and security
does my login in account need at a min to do what I just did? I'm sure that I
gave it more than what was needed, but wanted to make sure that it worked.
Thanks,
Wills
"wills" wrote:
> My development environment consists of 2 separate servers (sql 2000 server
> and iis6 server).
> SQL 2000 Server has everything installed on it per the Microsoft
> documentation.
> IIS6 Web Server has everything installed on it per the Micorosoft
> documentation.
> My workstation has VS 2003 with reporting services install in it.
> I followed an example in the Wrox SQL Server Reporting Services book.
> Everything was easy to follow and managed to deploy the report to the web
> server.
> I tried to access the report:
> http://192.168.1.4/reportserver?%2fMy+First+Report+Project%2fEmployee_List&rs:Command=Render
> Got the following in return:
> Reporting Services Error
> ----
> An error has occurred during report processing. (rsProcessingAborted) Get
> Online Help
> Cannot create a connection to data source 'AdventureWorks2000'.
> (rsErrorOpeningConnection) Get Online Help
> Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> Server connection.
> ----
> Microsoft Reporting Services
> I clicked on the links provided for help and got:
> Currently there are no Microsoft Knowledge Base articles available for this
> specific error or event message.
> I viewed my datasourcedefinition:
> --
> - <DataSourceDefinition>
> <Extension>SQL</Extension>
> <ConnectString>data source=DEV-SRV03;initial
> catalog=AdventureWorks2000</ConnectString>
> <CredentialRetrieval>Integrated</CredentialRetrieval>
> <Enabled>True</Enabled>
> </DataSourceDefinition>
> --
> What do I need to do? Can someone please help me resolve this problem?
> Thanks,
> Wills
and iis6 server).
SQL 2000 Server has everything installed on it per the Microsoft
documentation.
IIS6 Web Server has everything installed on it per the Micorosoft
documentation.
My workstation has VS 2003 with reporting services install in it.
I followed an example in the Wrox SQL Server Reporting Services book.
Everything was easy to follow and managed to deploy the report to the web
server.
I tried to access the report:
http://192.168.1.4/reportserver?%2fMy+First+Report+Project%2fEmployee_List&rs:Command=Render
Got the following in return:
Reporting Services Error
----
An error has occurred during report processing. (rsProcessingAborted) Get
Online Help
Cannot create a connection to data source 'AdventureWorks2000'.
(rsErrorOpeningConnection) Get Online Help
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
----
Microsoft Reporting Services
I clicked on the links provided for help and got:
Currently there are no Microsoft Knowledge Base articles available for this
specific error or event message.
I viewed my datasourcedefinition:
--
- <DataSourceDefinition>
<Extension>SQL</Extension>
<ConnectString>data source=DEV-SRV03;initial
catalog=AdventureWorks2000</ConnectString>
<CredentialRetrieval>Integrated</CredentialRetrieval>
<Enabled>True</Enabled>
</DataSourceDefinition>
--
What do I need to do? Can someone please help me resolve this problem?
Thanks,
WillsWell here is what I did to resolve the problem.
1. Deleted the 1st attempt.
2. Create a SQL account and gave it access to the 3 dbs: AdventureWorks2000,
ReportServer and ReportServerTempDB (role: sys admin; db access with dbowner).
3. Redid the tutorial and instead of Integrated security, I went with the
sql login account.
4. Next, I had to set OverwriteDataSources to True so that it would
overwrite my existing version.
5.
http://192.168.1.4/reportserver?%2fMy+First+Report+Project%2fEmployee_List&rs:Command=Render
Gave me the report I was looking for.
Now my question is on the sql login account. What permissions and security
does my login in account need at a min to do what I just did? I'm sure that I
gave it more than what was needed, but wanted to make sure that it worked.
Thanks,
Wills
"wills" wrote:
> My development environment consists of 2 separate servers (sql 2000 server
> and iis6 server).
> SQL 2000 Server has everything installed on it per the Microsoft
> documentation.
> IIS6 Web Server has everything installed on it per the Micorosoft
> documentation.
> My workstation has VS 2003 with reporting services install in it.
> I followed an example in the Wrox SQL Server Reporting Services book.
> Everything was easy to follow and managed to deploy the report to the web
> server.
> I tried to access the report:
> http://192.168.1.4/reportserver?%2fMy+First+Report+Project%2fEmployee_List&rs:Command=Render
> Got the following in return:
> Reporting Services Error
> ----
> An error has occurred during report processing. (rsProcessingAborted) Get
> Online Help
> Cannot create a connection to data source 'AdventureWorks2000'.
> (rsErrorOpeningConnection) Get Online Help
> Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> Server connection.
> ----
> Microsoft Reporting Services
> I clicked on the links provided for help and got:
> Currently there are no Microsoft Knowledge Base articles available for this
> specific error or event message.
> I viewed my datasourcedefinition:
> --
> - <DataSourceDefinition>
> <Extension>SQL</Extension>
> <ConnectString>data source=DEV-SRV03;initial
> catalog=AdventureWorks2000</ConnectString>
> <CredentialRetrieval>Integrated</CredentialRetrieval>
> <Enabled>True</Enabled>
> </DataSourceDefinition>
> --
> What do I need to do? Can someone please help me resolve this problem?
> Thanks,
> Wills
Login failed for user <everysqluser>
On two different days, June 7th and 28, our SQL error logs
for 9 of 11 SQL Servers shows failed login attempts for
each SQL user as listed in that server's logins. There
are 9 failed attempts for each syslogin. We are running
SQL 2000 with SP3 which indicates that we are protected by
Slammer. Has anyone heard of a Slammer variation that may
have started in June of 2004? Any other thoughts? JerryHaven't heard of anything like that recently. But could it be that someone
is trying to guess the passwords of your logins?
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Jerry" <anonymous@.discussions.microsoft.com> wrote in message
news:275f401c46381$f669b3f0$a501280a@.phx
.gbl...
> On two different days, June 7th and 28, our SQL error logs
> for 9 of 11 SQL Servers shows failed login attempts for
> each SQL user as listed in that server's logins. There
> are 9 failed attempts for each syslogin. We are running
> SQL 2000 with SP3 which indicates that we are protected by
> Slammer. Has anyone heard of a Slammer variation that may
> have started in June of 2004? Any other thoughts? Jerry|||Jerry,
Unless you have install SQL Security patch MS03-031, you probably are
NOT protected from the 'Slammer' worm.
Here is Microsoft's link for the 32-bit patch:
http://www.microsoft.com/downloads/...&displaylang=en
Another thought to consider, 'SQL Injection Tag' attacks. This would be a
possbile concern if any of these database servers, are servering up web
pages that ask for user login and password information.
Just a couple of thoughts.
HTH
"Jerry" <anonymous@.discussions.microsoft.com> wrote in message
news:275f401c46381$f669b3f0$a501280a@.phx
.gbl...
> On two different days, June 7th and 28, our SQL error logs
> for 9 of 11 SQL Servers shows failed login attempts for
> each SQL user as listed in that server's logins. There
> are 9 failed attempts for each syslogin. We are running
> SQL 2000 with SP3 which indicates that we are protected by
> Slammer. Has anyone heard of a Slammer variation that may
> have started in June of 2004? Any other thoughts? Jerry|||Consider using Windows Authentication if possible and review what users are
granted access to the server.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||No but one thought is that what you are seeing could just be
from someone running MBSA (Microsoft Baseline Security
Analyzer). I think it logs an entry in the application log
on the server so you may be able to track down what date,
time it ran.
-Sue
On Tue, 6 Jul 2004 10:52:10 -0700, "Jerry"
<anonymous@.discussions.microsoft.com> wrote:
>On two different days, June 7th and 28, our SQL error logs
>for 9 of 11 SQL Servers shows failed login attempts for
>each SQL user as listed in that server's logins. There
>are 9 failed attempts for each syslogin. We are running
>SQL 2000 with SP3 which indicates that we are protected by
>Slammer. Has anyone heard of a Slammer variation that may
>have started in June of 2004? Any other thoughts? Jerrysql
for 9 of 11 SQL Servers shows failed login attempts for
each SQL user as listed in that server's logins. There
are 9 failed attempts for each syslogin. We are running
SQL 2000 with SP3 which indicates that we are protected by
Slammer. Has anyone heard of a Slammer variation that may
have started in June of 2004? Any other thoughts? JerryHaven't heard of anything like that recently. But could it be that someone
is trying to guess the passwords of your logins?
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Jerry" <anonymous@.discussions.microsoft.com> wrote in message
news:275f401c46381$f669b3f0$a501280a@.phx
.gbl...
> On two different days, June 7th and 28, our SQL error logs
> for 9 of 11 SQL Servers shows failed login attempts for
> each SQL user as listed in that server's logins. There
> are 9 failed attempts for each syslogin. We are running
> SQL 2000 with SP3 which indicates that we are protected by
> Slammer. Has anyone heard of a Slammer variation that may
> have started in June of 2004? Any other thoughts? Jerry|||Jerry,
Unless you have install SQL Security patch MS03-031, you probably are
NOT protected from the 'Slammer' worm.
Here is Microsoft's link for the 32-bit patch:
http://www.microsoft.com/downloads/...&displaylang=en
Another thought to consider, 'SQL Injection Tag' attacks. This would be a
possbile concern if any of these database servers, are servering up web
pages that ask for user login and password information.
Just a couple of thoughts.
HTH
"Jerry" <anonymous@.discussions.microsoft.com> wrote in message
news:275f401c46381$f669b3f0$a501280a@.phx
.gbl...
> On two different days, June 7th and 28, our SQL error logs
> for 9 of 11 SQL Servers shows failed login attempts for
> each SQL user as listed in that server's logins. There
> are 9 failed attempts for each syslogin. We are running
> SQL 2000 with SP3 which indicates that we are protected by
> Slammer. Has anyone heard of a Slammer variation that may
> have started in June of 2004? Any other thoughts? Jerry|||Consider using Windows Authentication if possible and review what users are
granted access to the server.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||No but one thought is that what you are seeing could just be
from someone running MBSA (Microsoft Baseline Security
Analyzer). I think it logs an entry in the application log
on the server so you may be able to track down what date,
time it ran.
-Sue
On Tue, 6 Jul 2004 10:52:10 -0700, "Jerry"
<anonymous@.discussions.microsoft.com> wrote:
>On two different days, June 7th and 28, our SQL error logs
>for 9 of 11 SQL Servers shows failed login attempts for
>each SQL user as listed in that server's logins. There
>are 9 failed attempts for each syslogin. We are running
>SQL 2000 with SP3 which indicates that we are protected by
>Slammer. Has anyone heard of a Slammer variation that may
>have started in June of 2004? Any other thoughts? Jerrysql
Wednesday, March 21, 2012
login failed for user "null"
Hello,
I have problem with linked servers (MS SQL Server 2k)- when I try to execute
query(update, insert..) with linked servers, I have message Login failed for
user 'null'. This is very strange problem, some computers in AD (2k3 std)
don't have this error, when the other have this problem for few hours and
some have this error all the time. Network Protocols is TCP/IP.
Any idea ?Are the linked servers set up to "Be made using the login's current security
context"? If that's the case, then I'm guessing that you are having a
problem with delegation. Review the BOL topic "Security Account Delegation"
this will get you going in the right direction. You will need to have a
domain admin assist you with this if you do not have domain admin rights.
After running the setspn command, it may take a while for the change to take
effect. (at least it did in my environment)
"dobrzak" wrote:
> Hello,
> I have problem with linked servers (MS SQL Server 2k)- when I try to execu
te
> query(update, insert..) with linked servers, I have message Login failed f
or
> user 'null'. This is very strange problem, some computers in AD (2k3 std)
> don't have this error, when the other have this problem for few hours and
> some have this error all the time. Network Protocols is TCP/IP.
> Any idea ?
>
>sql
I have problem with linked servers (MS SQL Server 2k)- when I try to execute
query(update, insert..) with linked servers, I have message Login failed for
user 'null'. This is very strange problem, some computers in AD (2k3 std)
don't have this error, when the other have this problem for few hours and
some have this error all the time. Network Protocols is TCP/IP.
Any idea ?Are the linked servers set up to "Be made using the login's current security
context"? If that's the case, then I'm guessing that you are having a
problem with delegation. Review the BOL topic "Security Account Delegation"
this will get you going in the right direction. You will need to have a
domain admin assist you with this if you do not have domain admin rights.
After running the setspn command, it may take a while for the change to take
effect. (at least it did in my environment)
"dobrzak" wrote:
> Hello,
> I have problem with linked servers (MS SQL Server 2k)- when I try to execu
te
> query(update, insert..) with linked servers, I have message Login failed f
or
> user 'null'. This is very strange problem, some computers in AD (2k3 std)
> don't have this error, when the other have this problem for few hours and
> some have this error all the time. Network Protocols is TCP/IP.
> Any idea ?
>
>sql
Monday, March 19, 2012
Login Failed for Linked Server
In EM I am trying to create a link between two SQL SQL2K servers using NT
domain logins. It works using SQL Server logins, but not with NT logins.
The error message is "Error 18456: Login failed for user 'NT
AUTHORITY\ANONOYMUS LOGIN'. What am I doing wrong? I am the DBA on both
servers. I have checked the 'be made using the logins current security
context' box in the security tab. I can run querys against the second
server, but I need to run a linked query. We are trying to keep to NT logins
for everything, so I prefer not to use SQL logins.DaveK wrote:
> In EM I am trying to create a link between two SQL SQL2K servers using NT
> domain logins. It works using SQL Server logins, but not with NT logins.
> The error message is "Error 18456: Login failed for user 'NT
> AUTHORITY\ANONOYMUS LOGIN'. What am I doing wrong? I am the DBA on both
> servers. I have checked the 'be made using the logins current security
> context' box in the security tab. I can run querys against the second
> server, but I need to run a linked query. We are trying to keep to NT logins
> for everything, so I prefer not to use SQL logins.
Hi
Try to look up "delegation" in Books On Line. There are a few settings
in your domain that has to be in place in order to use NT login for
Linked servers.
Regards
Steen
domain logins. It works using SQL Server logins, but not with NT logins.
The error message is "Error 18456: Login failed for user 'NT
AUTHORITY\ANONOYMUS LOGIN'. What am I doing wrong? I am the DBA on both
servers. I have checked the 'be made using the logins current security
context' box in the security tab. I can run querys against the second
server, but I need to run a linked query. We are trying to keep to NT logins
for everything, so I prefer not to use SQL logins.DaveK wrote:
> In EM I am trying to create a link between two SQL SQL2K servers using NT
> domain logins. It works using SQL Server logins, but not with NT logins.
> The error message is "Error 18456: Login failed for user 'NT
> AUTHORITY\ANONOYMUS LOGIN'. What am I doing wrong? I am the DBA on both
> servers. I have checked the 'be made using the logins current security
> context' box in the security tab. I can run querys against the second
> server, but I need to run a linked query. We are trying to keep to NT logins
> for everything, so I prefer not to use SQL logins.
Hi
Try to look up "delegation" in Books On Line. There are a few settings
in your domain that has to be in place in order to use NT login for
Linked servers.
Regards
Steen
Login Failed for Linked Server
In EM I am trying to create a link between two SQL SQL2K servers using NT
domain logins. It works using SQL Server logins, but not with NT logins.
The error message is "Error 18456: Login failed for user 'NT
AUTHORITY\ANONOYMUS LOGIN'. What am I doing wrong? I am the DBA on both
servers. I have checked the 'be made using the logins current security
context' box in the security tab. I can run querys against the second
server, but I need to run a linked query. We are trying to keep to NT login
s
for everything, so I prefer not to use SQL logins.DaveK wrote:
> In EM I am trying to create a link between two SQL SQL2K servers using NT
> domain logins. It works using SQL Server logins, but not with NT logins.
> The error message is "Error 18456: Login failed for user 'NT
> AUTHORITY\ANONOYMUS LOGIN'. What am I doing wrong? I am the DBA on both
> servers. I have checked the 'be made using the logins current security
> context' box in the security tab. I can run querys against the second
> server, but I need to run a linked query. We are trying to keep to NT log
ins
> for everything, so I prefer not to use SQL logins.
Hi
Try to look up "delegation" in Books On Line. There are a few settings
in your domain that has to be in place in order to use NT login for
Linked servers.
Regards
Steen
domain logins. It works using SQL Server logins, but not with NT logins.
The error message is "Error 18456: Login failed for user 'NT
AUTHORITY\ANONOYMUS LOGIN'. What am I doing wrong? I am the DBA on both
servers. I have checked the 'be made using the logins current security
context' box in the security tab. I can run querys against the second
server, but I need to run a linked query. We are trying to keep to NT login
s
for everything, so I prefer not to use SQL logins.DaveK wrote:
> In EM I am trying to create a link between two SQL SQL2K servers using NT
> domain logins. It works using SQL Server logins, but not with NT logins.
> The error message is "Error 18456: Login failed for user 'NT
> AUTHORITY\ANONOYMUS LOGIN'. What am I doing wrong? I am the DBA on both
> servers. I have checked the 'be made using the logins current security
> context' box in the security tab. I can run querys against the second
> server, but I need to run a linked query. We are trying to keep to NT log
ins
> for everything, so I prefer not to use SQL logins.
Hi
Try to look up "delegation" in Books On Line. There are a few settings
in your domain that has to be in place in order to use NT login for
Linked servers.
Regards
Steen
Login Failed for Linked Server
In EM I am trying to create a link between two SQL SQL2K servers using NT
domain logins. It works using SQL Server logins, but not with NT logins.
The error message is "Error 18456: Login failed for user 'NT
AUTHORITY\ANONOYMUS LOGIN'. What am I doing wrong? I am the DBA on both
servers. I have checked the 'be made using the logins current security
context' box in the security tab. I can run querys against the second
server, but I need to run a linked query. We are trying to keep to NT logins
for everything, so I prefer not to use SQL logins.
DaveK wrote:
> In EM I am trying to create a link between two SQL SQL2K servers using NT
> domain logins. It works using SQL Server logins, but not with NT logins.
> The error message is "Error 18456: Login failed for user 'NT
> AUTHORITY\ANONOYMUS LOGIN'. What am I doing wrong? I am the DBA on both
> servers. I have checked the 'be made using the logins current security
> context' box in the security tab. I can run querys against the second
> server, but I need to run a linked query. We are trying to keep to NT logins
> for everything, so I prefer not to use SQL logins.
Hi
Try to look up "delegation" in Books On Line. There are a few settings
in your domain that has to be in place in order to use NT login for
Linked servers.
Regards
Steen
domain logins. It works using SQL Server logins, but not with NT logins.
The error message is "Error 18456: Login failed for user 'NT
AUTHORITY\ANONOYMUS LOGIN'. What am I doing wrong? I am the DBA on both
servers. I have checked the 'be made using the logins current security
context' box in the security tab. I can run querys against the second
server, but I need to run a linked query. We are trying to keep to NT logins
for everything, so I prefer not to use SQL logins.
DaveK wrote:
> In EM I am trying to create a link between two SQL SQL2K servers using NT
> domain logins. It works using SQL Server logins, but not with NT logins.
> The error message is "Error 18456: Login failed for user 'NT
> AUTHORITY\ANONOYMUS LOGIN'. What am I doing wrong? I am the DBA on both
> servers. I have checked the 'be made using the logins current security
> context' box in the security tab. I can run querys against the second
> server, but I need to run a linked query. We are trying to keep to NT logins
> for everything, so I prefer not to use SQL logins.
Hi
Try to look up "delegation" in Books On Line. There are a few settings
in your domain that has to be in place in order to use NT login for
Linked servers.
Regards
Steen
Login failed connecting to new principal after failover
Hello,
I have setup database mirroring on two Windows 2003 R2 x64 servers using SQL Server 2005 SP1 Developer Edition. Our application is connecting to SQL Server using a SQL Server login. The application is using ADO and SQL Native Client to connect to the server. After a failover, our app attempts to reconnect to the database. The reconnect fails with the error:
Cannot open database "db1" requested by the login. The login failed.
The login is not associated with a user in the new principal database. I run sp_change_users_login to reconnect the user and login. sp_change_users_login says that it fixed 1 orphaned user. Our app then reconnects successfully.
I have tried several failovers, and each time I see the same behavior. The association between the login and user gets lost.
The issue is definitely with the login. I tried using sa to connect to the database, and then our app was able to reconnect after a failover.
Is this a known issue with database mirroring? Is it fixed in SP2?
Thanks,
Heather
Hi Heather. How are you creating the sql logins on the mirror server? If you create them with the same SID as they have on the principal server, that should address the issue. In Sql 2005, you specify the SID for a sql login using the new 'create login' syntax, so it would look something like follows:
create login <loginname> with password = <password>, sid = <sid for same login on principal server>,...
HTH,
|||Thanks, Chad. That resolved the problem.
Heather
Monday, March 12, 2012
Login error: 18456, Severity: 14, State: 11.
Hi,
we did an clean install of MOSS2007 and SQL2005. Both on different servers.
Everything worked well and suddenly we are getting this error and MOSS2007
stops working. The second message in the error log is: 'Login failed for user
'Domain\user'.
If I put the user in the SysAdmin serverrole they are able to connect.
Strange thing is that this is also happening on our SP2003/SQL2000 servers.
But on these servers it's gone after an short period.
Someone any idea?
--
Robert Hartskeerl, MCTS, MCITPHi Robert
State 11 is "Login is valid, but server access failed." my guess is that the
default database is wrong or you don't have access to it. You could try
logging in as that user and use SQLCMD or osql to change the database you
connect to. sp_defaultdb can be used to change the default database.
John
"Robert Hartskeerl" wrote:
> Hi,
> we did an clean install of MOSS2007 and SQL2005. Both on different servers.
> Everything worked well and suddenly we are getting this error and MOSS2007
> stops working. The second message in the error log is: 'Login failed for user
> 'Domain\user'.
> If I put the user in the SysAdmin serverrole they are able to connect.
> Strange thing is that this is also happening on our SP2003/SQL2000 servers.
> But on these servers it's gone after an short period.
> Someone any idea?
> --
> Robert Hartskeerl, MCTS, MCITP|||Hello, Robert
Maybe one of following will help:
http://blogs.msdn.com/sql_protocols/archive/2007/06/18/connecting-to-sql-server-2005-on-vista-and-longhorn.aspx
http://blogs.msdn.com/sql_protocols/archive/2006/02/21/536201.aspx
Razvan Socol
SQL Server MVP|||Thanks for your response John.
We already solved this with the information on connect.microsoft.com.
We had created extra tcp enpoints for different users. What happens is that
SQL Server removes the public role from TCP-Connect. So while the other
applications that used the non-default endpoints only sa's are able to
connect to the default endpoint.
After adding TCP-Endpoint to the public role we can connect.
Robert Hartskeerl
--
Robert Hartskeerl, MCTS, MCITP
"John Bell" wrote:
> Hi Robert
> State 11 is "Login is valid, but server access failed." my guess is that the
> default database is wrong or you don't have access to it. You could try
> logging in as that user and use SQLCMD or osql to change the database you
> connect to. sp_defaultdb can be used to change the default database.
> John
>
> "Robert Hartskeerl" wrote:
> > Hi,
> >
> > we did an clean install of MOSS2007 and SQL2005. Both on different servers.
> > Everything worked well and suddenly we are getting this error and MOSS2007
> > stops working. The second message in the error log is: 'Login failed for user
> > 'Domain\user'.
> >
> > If I put the user in the SysAdmin serverrole they are able to connect.
> >
> > Strange thing is that this is also happening on our SP2003/SQL2000 servers.
> > But on these servers it's gone after an short period.
> >
> > Someone any idea?
> > --
> > Robert Hartskeerl, MCTS, MCITP
we did an clean install of MOSS2007 and SQL2005. Both on different servers.
Everything worked well and suddenly we are getting this error and MOSS2007
stops working. The second message in the error log is: 'Login failed for user
'Domain\user'.
If I put the user in the SysAdmin serverrole they are able to connect.
Strange thing is that this is also happening on our SP2003/SQL2000 servers.
But on these servers it's gone after an short period.
Someone any idea?
--
Robert Hartskeerl, MCTS, MCITPHi Robert
State 11 is "Login is valid, but server access failed." my guess is that the
default database is wrong or you don't have access to it. You could try
logging in as that user and use SQLCMD or osql to change the database you
connect to. sp_defaultdb can be used to change the default database.
John
"Robert Hartskeerl" wrote:
> Hi,
> we did an clean install of MOSS2007 and SQL2005. Both on different servers.
> Everything worked well and suddenly we are getting this error and MOSS2007
> stops working. The second message in the error log is: 'Login failed for user
> 'Domain\user'.
> If I put the user in the SysAdmin serverrole they are able to connect.
> Strange thing is that this is also happening on our SP2003/SQL2000 servers.
> But on these servers it's gone after an short period.
> Someone any idea?
> --
> Robert Hartskeerl, MCTS, MCITP|||Hello, Robert
Maybe one of following will help:
http://blogs.msdn.com/sql_protocols/archive/2007/06/18/connecting-to-sql-server-2005-on-vista-and-longhorn.aspx
http://blogs.msdn.com/sql_protocols/archive/2006/02/21/536201.aspx
Razvan Socol
SQL Server MVP|||Thanks for your response John.
We already solved this with the information on connect.microsoft.com.
We had created extra tcp enpoints for different users. What happens is that
SQL Server removes the public role from TCP-Connect. So while the other
applications that used the non-default endpoints only sa's are able to
connect to the default endpoint.
After adding TCP-Endpoint to the public role we can connect.
Robert Hartskeerl
--
Robert Hartskeerl, MCTS, MCITP
"John Bell" wrote:
> Hi Robert
> State 11 is "Login is valid, but server access failed." my guess is that the
> default database is wrong or you don't have access to it. You could try
> logging in as that user and use SQLCMD or osql to change the database you
> connect to. sp_defaultdb can be used to change the default database.
> John
>
> "Robert Hartskeerl" wrote:
> > Hi,
> >
> > we did an clean install of MOSS2007 and SQL2005. Both on different servers.
> > Everything worked well and suddenly we are getting this error and MOSS2007
> > stops working. The second message in the error log is: 'Login failed for user
> > 'Domain\user'.
> >
> > If I put the user in the SysAdmin serverrole they are able to connect.
> >
> > Strange thing is that this is also happening on our SP2003/SQL2000 servers.
> > But on these servers it's gone after an short period.
> >
> > Someone any idea?
> > --
> > Robert Hartskeerl, MCTS, MCITP
Friday, March 9, 2012
Login account in two servers
Dear All,
The background is: I create a set of login account in the
production SQL 2K server (namely A) and assign some roles
to them. I usually backup the database and restore it to
our backup SQL 2K server (namely B) daily.
I would like to create the same set of login acount in B
server but it usally prompt me that there is already an
account in the related database. I believe this is
because I restore the whole database from A to B. In SQL
6.5, I will do a trick to update the uid/sid so that both
server of those login account are the same but in SQL 2K,
it dosen't allow me to do so.
Any idea?
Thanks.
Hi,
Looks like the Login and user chan has lost. To bring up the chain you could
use the stored procedure "sp_change_users_login". Have a look in SQL server
books online for the usage of the stored procedure.
Thanks
Hari
MCDBA
"Charles Lee" <leech@.dnb.com> wrote in message
news:49eb01c47389$30889df0$a401280a@.phx.gbl...
> Dear All,
> The background is: I create a set of login account in the
> production SQL 2K server (namely A) and assign some roles
> to them. I usually backup the database and restore it to
> our backup SQL 2K server (namely B) daily.
> I would like to create the same set of login acount in B
> server but it usally prompt me that there is already an
> account in the related database. I believe this is
> because I restore the whole database from A to B. In SQL
> 6.5, I will do a trick to update the uid/sid so that both
> server of those login account are the same but in SQL 2K,
> it dosen't allow me to do so.
> Any idea?
> Thanks.
|||In books on line, look up "log shipping". While changing the roles for log
shipping, one must ensure that the new server has all of the logins, etc
from the old server. There is really nice documentation about all of the
steps necessary to do this.. ( including the use of sp_change_users_login)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Charles Lee" <leech@.dnb.com> wrote in message
news:49eb01c47389$30889df0$a401280a@.phx.gbl...
> Dear All,
> The background is: I create a set of login account in the
> production SQL 2K server (namely A) and assign some roles
> to them. I usually backup the database and restore it to
> our backup SQL 2K server (namely B) daily.
> I would like to create the same set of login acount in B
> server but it usally prompt me that there is already an
> account in the related database. I believe this is
> because I restore the whole database from A to B. In SQL
> 6.5, I will do a trick to update the uid/sid so that both
> server of those login account are the same but in SQL 2K,
> it dosen't allow me to do so.
> Any idea?
> Thanks.
The background is: I create a set of login account in the
production SQL 2K server (namely A) and assign some roles
to them. I usually backup the database and restore it to
our backup SQL 2K server (namely B) daily.
I would like to create the same set of login acount in B
server but it usally prompt me that there is already an
account in the related database. I believe this is
because I restore the whole database from A to B. In SQL
6.5, I will do a trick to update the uid/sid so that both
server of those login account are the same but in SQL 2K,
it dosen't allow me to do so.
Any idea?
Thanks.
Hi,
Looks like the Login and user chan has lost. To bring up the chain you could
use the stored procedure "sp_change_users_login". Have a look in SQL server
books online for the usage of the stored procedure.
Thanks
Hari
MCDBA
"Charles Lee" <leech@.dnb.com> wrote in message
news:49eb01c47389$30889df0$a401280a@.phx.gbl...
> Dear All,
> The background is: I create a set of login account in the
> production SQL 2K server (namely A) and assign some roles
> to them. I usually backup the database and restore it to
> our backup SQL 2K server (namely B) daily.
> I would like to create the same set of login acount in B
> server but it usally prompt me that there is already an
> account in the related database. I believe this is
> because I restore the whole database from A to B. In SQL
> 6.5, I will do a trick to update the uid/sid so that both
> server of those login account are the same but in SQL 2K,
> it dosen't allow me to do so.
> Any idea?
> Thanks.
|||In books on line, look up "log shipping". While changing the roles for log
shipping, one must ensure that the new server has all of the logins, etc
from the old server. There is really nice documentation about all of the
steps necessary to do this.. ( including the use of sp_change_users_login)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Charles Lee" <leech@.dnb.com> wrote in message
news:49eb01c47389$30889df0$a401280a@.phx.gbl...
> Dear All,
> The background is: I create a set of login account in the
> production SQL 2K server (namely A) and assign some roles
> to them. I usually backup the database and restore it to
> our backup SQL 2K server (namely B) daily.
> I would like to create the same set of login acount in B
> server but it usally prompt me that there is already an
> account in the related database. I believe this is
> because I restore the whole database from A to B. In SQL
> 6.5, I will do a trick to update the uid/sid so that both
> server of those login account are the same but in SQL 2K,
> it dosen't allow me to do so.
> Any idea?
> Thanks.
Login account in two servers
Dear All,
The background is: I create a set of login account in the
production SQL 2K server (namely A) and assign some roles
to them. I usually backup the database and restore it to
our backup SQL 2K server (namely B) daily.
I would like to create the same set of login acount in B
server but it usally prompt me that there is already an
account in the related database. I believe this is
because I restore the whole database from A to B. In SQL
6.5, I will do a trick to update the uid/sid so that both
server of those login account are the same but in SQL 2K,
it dosen't allow me to do so.
Any idea?
Thanks.Hi,
Looks like the Login and user chan has lost. To bring up the chain you could
use the stored procedure "sp_change_users_login". Have a look in SQL server
books online for the usage of the stored procedure.
Thanks
Hari
MCDBA
"Charles Lee" <leech@.dnb.com> wrote in message
news:49eb01c47389$30889df0$a401280a@.phx.gbl...
> Dear All,
> The background is: I create a set of login account in the
> production SQL 2K server (namely A) and assign some roles
> to them. I usually backup the database and restore it to
> our backup SQL 2K server (namely B) daily.
> I would like to create the same set of login acount in B
> server but it usally prompt me that there is already an
> account in the related database. I believe this is
> because I restore the whole database from A to B. In SQL
> 6.5, I will do a trick to update the uid/sid so that both
> server of those login account are the same but in SQL 2K,
> it dosen't allow me to do so.
> Any idea?
> Thanks.|||In books on line, look up "log shipping". While changing the roles for log
shipping, one must ensure that the new server has all of the logins, etc
from the old server. There is really nice documentation about all of the
steps necessary to do this.. ( including the use of sp_change_users_login)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Charles Lee" <leech@.dnb.com> wrote in message
news:49eb01c47389$30889df0$a401280a@.phx.gbl...
> Dear All,
> The background is: I create a set of login account in the
> production SQL 2K server (namely A) and assign some roles
> to them. I usually backup the database and restore it to
> our backup SQL 2K server (namely B) daily.
> I would like to create the same set of login acount in B
> server but it usally prompt me that there is already an
> account in the related database. I believe this is
> because I restore the whole database from A to B. In SQL
> 6.5, I will do a trick to update the uid/sid so that both
> server of those login account are the same but in SQL 2K,
> it dosen't allow me to do so.
> Any idea?
> Thanks.
The background is: I create a set of login account in the
production SQL 2K server (namely A) and assign some roles
to them. I usually backup the database and restore it to
our backup SQL 2K server (namely B) daily.
I would like to create the same set of login acount in B
server but it usally prompt me that there is already an
account in the related database. I believe this is
because I restore the whole database from A to B. In SQL
6.5, I will do a trick to update the uid/sid so that both
server of those login account are the same but in SQL 2K,
it dosen't allow me to do so.
Any idea?
Thanks.Hi,
Looks like the Login and user chan has lost. To bring up the chain you could
use the stored procedure "sp_change_users_login". Have a look in SQL server
books online for the usage of the stored procedure.
Thanks
Hari
MCDBA
"Charles Lee" <leech@.dnb.com> wrote in message
news:49eb01c47389$30889df0$a401280a@.phx.gbl...
> Dear All,
> The background is: I create a set of login account in the
> production SQL 2K server (namely A) and assign some roles
> to them. I usually backup the database and restore it to
> our backup SQL 2K server (namely B) daily.
> I would like to create the same set of login acount in B
> server but it usally prompt me that there is already an
> account in the related database. I believe this is
> because I restore the whole database from A to B. In SQL
> 6.5, I will do a trick to update the uid/sid so that both
> server of those login account are the same but in SQL 2K,
> it dosen't allow me to do so.
> Any idea?
> Thanks.|||In books on line, look up "log shipping". While changing the roles for log
shipping, one must ensure that the new server has all of the logins, etc
from the old server. There is really nice documentation about all of the
steps necessary to do this.. ( including the use of sp_change_users_login)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Charles Lee" <leech@.dnb.com> wrote in message
news:49eb01c47389$30889df0$a401280a@.phx.gbl...
> Dear All,
> The background is: I create a set of login account in the
> production SQL 2K server (namely A) and assign some roles
> to them. I usually backup the database and restore it to
> our backup SQL 2K server (namely B) daily.
> I would like to create the same set of login acount in B
> server but it usally prompt me that there is already an
> account in the related database. I believe this is
> because I restore the whole database from A to B. In SQL
> 6.5, I will do a trick to update the uid/sid so that both
> server of those login account are the same but in SQL 2K,
> it dosen't allow me to do so.
> Any idea?
> Thanks.
Login account in two servers
Dear All,
The background is: I create a set of login account in the
production SQL 2K server (namely A) and assign some roles
to them. I usually backup the database and restore it to
our backup SQL 2K server (namely B) daily.
I would like to create the same set of login acount in B
server but it usally prompt me that there is already an
account in the related database. I believe this is
because I restore the whole database from A to B. In SQL
6.5, I will do a trick to update the uid/sid so that both
server of those login account are the same but in SQL 2K,
it dosen't allow me to do so.
Any idea?
Thanks.Hi,
Looks like the Login and user chan has lost. To bring up the chain you could
use the stored procedure "sp_change_users_login". Have a look in SQL server
books online for the usage of the stored procedure.
Thanks
Hari
MCDBA
"Charles Lee" <leech@.dnb.com> wrote in message
news:49eb01c47389$30889df0$a401280a@.phx.gbl...
> Dear All,
> The background is: I create a set of login account in the
> production SQL 2K server (namely A) and assign some roles
> to them. I usually backup the database and restore it to
> our backup SQL 2K server (namely B) daily.
> I would like to create the same set of login acount in B
> server but it usally prompt me that there is already an
> account in the related database. I believe this is
> because I restore the whole database from A to B. In SQL
> 6.5, I will do a trick to update the uid/sid so that both
> server of those login account are the same but in SQL 2K,
> it dosen't allow me to do so.
> Any idea?
> Thanks.|||In books on line, look up "log shipping". While changing the roles for log
shipping, one must ensure that the new server has all of the logins, etc
from the old server. There is really nice documentation about all of the
steps necessary to do this.. ( including the use of sp_change_users_login)
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Charles Lee" <leech@.dnb.com> wrote in message
news:49eb01c47389$30889df0$a401280a@.phx.gbl...
> Dear All,
> The background is: I create a set of login account in the
> production SQL 2K server (namely A) and assign some roles
> to them. I usually backup the database and restore it to
> our backup SQL 2K server (namely B) daily.
> I would like to create the same set of login acount in B
> server but it usally prompt me that there is already an
> account in the related database. I believe this is
> because I restore the whole database from A to B. In SQL
> 6.5, I will do a trick to update the uid/sid so that both
> server of those login account are the same but in SQL 2K,
> it dosen't allow me to do so.
> Any idea?
> Thanks.
The background is: I create a set of login account in the
production SQL 2K server (namely A) and assign some roles
to them. I usually backup the database and restore it to
our backup SQL 2K server (namely B) daily.
I would like to create the same set of login acount in B
server but it usally prompt me that there is already an
account in the related database. I believe this is
because I restore the whole database from A to B. In SQL
6.5, I will do a trick to update the uid/sid so that both
server of those login account are the same but in SQL 2K,
it dosen't allow me to do so.
Any idea?
Thanks.Hi,
Looks like the Login and user chan has lost. To bring up the chain you could
use the stored procedure "sp_change_users_login". Have a look in SQL server
books online for the usage of the stored procedure.
Thanks
Hari
MCDBA
"Charles Lee" <leech@.dnb.com> wrote in message
news:49eb01c47389$30889df0$a401280a@.phx.gbl...
> Dear All,
> The background is: I create a set of login account in the
> production SQL 2K server (namely A) and assign some roles
> to them. I usually backup the database and restore it to
> our backup SQL 2K server (namely B) daily.
> I would like to create the same set of login acount in B
> server but it usally prompt me that there is already an
> account in the related database. I believe this is
> because I restore the whole database from A to B. In SQL
> 6.5, I will do a trick to update the uid/sid so that both
> server of those login account are the same but in SQL 2K,
> it dosen't allow me to do so.
> Any idea?
> Thanks.|||In books on line, look up "log shipping". While changing the roles for log
shipping, one must ensure that the new server has all of the logins, etc
from the old server. There is really nice documentation about all of the
steps necessary to do this.. ( including the use of sp_change_users_login)
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Charles Lee" <leech@.dnb.com> wrote in message
news:49eb01c47389$30889df0$a401280a@.phx.gbl...
> Dear All,
> The background is: I create a set of login account in the
> production SQL 2K server (namely A) and assign some roles
> to them. I usually backup the database and restore it to
> our backup SQL 2K server (namely B) daily.
> I would like to create the same set of login acount in B
> server but it usally prompt me that there is already an
> account in the related database. I believe this is
> because I restore the whole database from A to B. In SQL
> 6.5, I will do a trick to update the uid/sid so that both
> server of those login account are the same but in SQL 2K,
> it dosen't allow me to do so.
> Any idea?
> Thanks.
Logical Records
The organisation I'm working for has SQL Server 2005 merge replication up and running on 11 servers in 3 different countries.
Ocasionally we experience problems caused by rows in a logical record relationship being replicated between servers in the wrong order, i.e. an attempt is made to insert child rows before the parent. I am not able to reproduce this behaviour on my test servers.
Has anyone else experienced this problem?
It's entirely possible for a child row to be inserted before parent row, but it should be greatly reduced in SQL 2005. What kind of errors are you having?|||i'm sorry, i misspoke. the whole point of logical records is that a logical record relationship is committed in one transaction, the whole logical record should be committed as one transaction.
Can you tell me what the errors are that you're seeing, and can you verify that the article that's failing is marked for logical records?
|||We have a master database filtered and published to 2 separate databases on the same cluster (overlapping partitions).
Each of these 2 databases is republished to another 4 servers, where it is republished again.
Users update a local SQL Express database which is programmatically synchronised (anonymous pull subscription, via RMO) with one of the 4 servers when a transaction completes.
Very occasionally, if a user updates an existing parent row and then adds a new parent row with all its children, and both programmatic synchronisations occur within the 60 second polling interval on the server, then the data is passed up to the cluster as a parent update, child inserts and a parent insert, in that order. As a result the child rows in the server and user databases are deleted. I have not been able to reproduce the problem in the test environment but I have enough logging in place to know exactly what's happening.
I know that the logical row relationships exist because I can see them in sysmergesubsetfilters but sp_helpmergefilter won't show them to me unless they're filter_type 3 (filter and logical record relationship).
|||Sorry but it's not clear to me what the problem is. If you update parent row with ID1, and insert new parent row with ID2 and child row with FK=ID2, why would the child rows in the server and user dbs get deleted? And for which ID are they getting deleted, ID1? Are you saying the updates are moving the row out of partition?|||The child rows for ID2 get deleted.
Inserting the child rows into the 3rd level database is not a problem because we have NOT FOR REPLICATION on the foreign keys.
When the 2nd level merge agent runs it receives the child rows before the parent and therefore appears not to be able to determine which partition they belong to, so deletes them.
Incidentally, we have the NOT FOR REPLICATION clause to prevent the converse problem, where child rows get deleted before the parent.
Currently all article filters are type 3 (filter and logical record). Changing filter type on the 2nd and 3rd levels to 2 (logical record only) appears to make no difference in my test environment because I can't reproduce the problem to start with.
|||That should be:
Incidentally, we have the NOT FOR REPLICATION clause to prevent the converse problem, where child rows get deleted *after* the parent.
|||Books Online says NFR (Not For Replication) cannot be placed on FK constraint involved with logical records. Are you sure the articles are involved in logical record relationship, that's what it seems you're implying above. Logical records should be commiting the whole thing as one txn, so even if the child goes before the parent, it shouldn't matter in a single transaction. Can you verify that the articles involved are indeed part of logical records?
|||OK, I read sp_addmergearticle in BOL and understand what option 3 is, I guess this would allow you to add NFR to your FK constraint. May I ask why you have it set to 3 instead of 2?|||It wasn't intentional.
I scripted the sp_addmergearticle definitions from the top-level database and used them. It seemed to work, at least to start with....
|||Can I have the URL for that BOL reference please?
I believe the articles are involved in a logical record relationship because I can see them in sysmergesubsetfilters and because sp_helpmergefilter says so.
However, I take your point that the relationship might not be working at all as opposed to working intermittently although I'm surprised at how rarely it causes a problem.
|||See topic "Grouping Changes to Related Rows with Logical Records", http://msdn2.microsoft.com/en-us/library/ms152507.aspx.
Easiest way to fix the problem is to set @.filter_type = 2, this will ensure your relational changes get committed all at once. Other than that, the fact that a child row goes before a parent row is by design (as long as @.filter_type <> 2), it's possible for this to happen. Do know that this doesn't pose any pure data loss, it just seems that way. This usually happens with some weird timing issue, or if your connection gets broken mid-sync. What happens is the child row insert gets to the publisher, the publisher will see the child row originated from the subscriber, but knows there's no parent row to match the filter criteria, thus send a delete back down to the subscriber. But as soon as the parent row makes it to the publisher, it will recognize the child row doesn't exist at the subscriber and send the child insert back down to the subscriber. This works only if NFR is enabled, which I believe is what you have, without it you'd probably get conflicts.
|||Thanks.
We have this change scheduled for 29/5 (due to the need to re-initialise subscriptions) and I'll let you know what happens.
|||Problem not solved.
We completely rebuilt the replication infrastructure from scratch using filter_type 2 and with no NFR clause on the foreign keys supporting the logical record relationships.
What we're now seeing is very clearly a bug in SQL Server.
If the republisher begins synchronising with the publisher and, before it finishes, a client synchronises its anonynous pull subscription with the republisher, then some of the client changes are lost (present on the republisher but never uploaded to the publisher)
Here are the messages captured during programmatic synchronisation on the client:
06/06/2007 11:15:32 Initializing
06/06/2007 11:15:32 Connecting to Publisher 'TT-SQL1\SQL2005'
06/06/2007 11:15:32 Retrieving publication information
06/06/2007 11:15:32 Retrieving subscription information.
06/06/2007 11:15:32 Uploading data changes to the Publisher
06/06/2007 11:15:33 Enumerating deletes in all articles
06/06/2007 11:15:33 Enumerating inserts and updates in article 'Team_Session'
06/06/2007 11:15:33 Uploaded 1 change(s) in 'Outing' (1 update): 1 total
06/06/2007 11:15:33 Uploaded 1 change(s) in 'Stint' (1 update): 1 total
06/06/2007 11:15:33 Uploaded 1 change(s) in 'Lap' (1 insert): 1 total
06/06/2007 11:15:33 Downloading data changes to the Subscriber
06/06/2007 11:15:34 Merge completed after processing 3 data change(s) (1 insert(s), 2 update(s), 0 delete(s), 0 conflict(s)).
06/06/2007 11:15:34 Synchronisation complete
06/06/2007 11:15:59 Initializing
06/06/2007 11:15:59 Connecting to Publisher 'TT-SQL1\SQL2005'
06/06/2007 11:15:59 Retrieving publication information
06/06/2007 11:15:59 Retrieving subscription information.
06/06/2007 11:16:00 Uploading data changes to the Publisher
06/06/2007 11:16:01 Enumerating deletes in all articles
06/06/2007 11:16:01 Enumerating inserts and updates in article 'Team_Event'
06/06/2007 11:16:01 Enumerating inserts and updates in article 'Team_Session'
06/06/2007 11:16:01 Uploaded 1 change(s) in 'EventTyreset' (1 update): 1 total
06/06/2007 11:16:01 Uploaded 1 change(s) in 'Outing' (1 insert): 1 total
06/06/2007 11:16:01 Uploaded 27 change(s) in 'Outing_AeroComponent' (27 inserts): 27 total
06/06/2007 11:16:01 Uploaded 8 change(s) in 'Outing_CofGComponent' (8 inserts): 8 total
06/06/2007 11:16:01 Uploaded 1 change(s) in 'Stint' (1 insert): 1 total
06/06/2007 11:16:01 Uploaded 16 change(s) in 'Outing_BallastComponent' (16 inserts): 16 total
06/06/2007 11:16:01 Downloading data changes to the Subscriber
06/06/2007 11:16:02 Merge completed after processing 54 data change(s) (53 insert(s), 1 update(s), 0 delete(s), 0 conflict(s)).
06/06/2007 11:16:02 Synchronisation complete
And here's what happened on the republisher (in reverse chronological order):
No data needed to be merged.
06/06/2007 11:19:54
No data needed to be merged.
06/06/2007 11:19:41
No data needed to be merged.
06/06/2007 11:18:30
No data needed to be merged.
06/06/2007 11:18:18
Uploaded 1 change(s) in 'EventTyreset' (1 update): 11 total
06/06/2007 11:18:17
Uploading data changes to the Publisher
06/06/2007 11:16:48
No data needed to be merged.
06/06/2007 11:16:32
Uploaded 1 change(s) in 'Lap' (1 insert): 4 total
06/06/2007 11:16:27
Uploaded 1 change(s) in 'Stint' (1 update): 13 total
06/06/2007 11:16:25
Uploaded 1 change(s) in 'Outing' (1 update): 13 total
06/06/2007 11:16:19
Uploading data changes to the Publisher
06/06/2007 11:14:50
No data needed to be merged.
I am desperate to get a solution to this problem. Are there any post-SP2 hot fixes affecting replication?
Thanks in advance.
|||We are seeing something very similar. Updates from a CE database (SDF file) are coming up to the republisher database, but not getting to the Master database. Results are inconsistent at best. The replication seems to be working properly if a direct insert is done at either the republisher level or the Master level. We have not seen a failure in that scenario. We would also like an answer to the problem.Logical Records
The organisation I'm working for has SQL Server 2005 merge replication up and running on 11 servers in 3 different countries.
Ocasionally we experience problems caused by rows in a logical record relationship being replicated between servers in the wrong order, i.e. an attempt is made to insert child rows before the parent. I am not able to reproduce this behaviour on my test servers.
Has anyone else experienced this problem?
It's entirely possible for a child row to be inserted before parent row, but it should be greatly reduced in SQL 2005. What kind of errors are you having?|||i'm sorry, i misspoke. the whole point of logical records is that a logical record relationship is committed in one transaction, the whole logical record should be committed as one transaction.
Can you tell me what the errors are that you're seeing, and can you verify that the article that's failing is marked for logical records?
|||We have a master database filtered and published to 2 separate databases on the same cluster (overlapping partitions).
Each of these 2 databases is republished to another 4 servers, where it is republished again.
Users update a local SQL Express database which is programmatically synchronised (anonymous pull subscription, via RMO) with one of the 4 servers when a transaction completes.
Very occasionally, if a user updates an existing parent row and then adds a new parent row with all its children, and both programmatic synchronisations occur within the 60 second polling interval on the server, then the data is passed up to the cluster as a parent update, child inserts and a parent insert, in that order. As a result the child rows in the server and user databases are deleted. I have not been able to reproduce the problem in the test environment but I have enough logging in place to know exactly what's happening.
I know that the logical row relationships exist because I can see them in sysmergesubsetfilters but sp_helpmergefilter won't show them to me unless they're filter_type 3 (filter and logical record relationship).
|||Sorry but it's not clear to me what the problem is. If you update parent row with ID1, and insert new parent row with ID2 and child row with FK=ID2, why would the child rows in the server and user dbs get deleted? And for which ID are they getting deleted, ID1? Are you saying the updates are moving the row out of partition?|||The child rows for ID2 get deleted.
Inserting the child rows into the 3rd level database is not a problem because we have NOT FOR REPLICATION on the foreign keys.
When the 2nd level merge agent runs it receives the child rows before the parent and therefore appears not to be able to determine which partition they belong to, so deletes them.
Incidentally, we have the NOT FOR REPLICATION clause to prevent the converse problem, where child rows get deleted before the parent.
Currently all article filters are type 3 (filter and logical record). Changing filter type on the 2nd and 3rd levels to 2 (logical record only) appears to make no difference in my test environment because I can't reproduce the problem to start with.
|||That should be:
Incidentally, we have the NOT FOR REPLICATION clause to prevent the converse problem, where child rows get deleted *after* the parent.
|||Books Online says NFR (Not For Replication) cannot be placed on FK constraint involved with logical records. Are you sure the articles are involved in logical record relationship, that's what it seems you're implying above. Logical records should be commiting the whole thing as one txn, so even if the child goes before the parent, it shouldn't matter in a single transaction. Can you verify that the articles involved are indeed part of logical records?
|||OK, I read sp_addmergearticle in BOL and understand what option 3 is, I guess this would allow you to add NFR to your FK constraint. May I ask why you have it set to 3 instead of 2?|||It wasn't intentional.
I scripted the sp_addmergearticle definitions from the top-level database and used them. It seemed to work, at least to start with....
|||Can I have the URL for that BOL reference please?
I believe the articles are involved in a logical record relationship because I can see them in sysmergesubsetfilters and because sp_helpmergefilter says so.
However, I take your point that the relationship might not be working at all as opposed to working intermittently although I'm surprised at how rarely it causes a problem.
|||See topic "Grouping Changes to Related Rows with Logical Records", http://msdn2.microsoft.com/en-us/library/ms152507.aspx.
Easiest way to fix the problem is to set @.filter_type = 2, this will ensure your relational changes get committed all at once. Other than that, the fact that a child row goes before a parent row is by design (as long as @.filter_type <> 2), it's possible for this to happen. Do know that this doesn't pose any pure data loss, it just seems that way. This usually happens with some weird timing issue, or if your connection gets broken mid-sync. What happens is the child row insert gets to the publisher, the publisher will see the child row originated from the subscriber, but knows there's no parent row to match the filter criteria, thus send a delete back down to the subscriber. But as soon as the parent row makes it to the publisher, it will recognize the child row doesn't exist at the subscriber and send the child insert back down to the subscriber. This works only if NFR is enabled, which I believe is what you have, without it you'd probably get conflicts.
|||Thanks.
We have this change scheduled for 29/5 (due to the need to re-initialise subscriptions) and I'll let you know what happens.
|||Problem not solved.
We completely rebuilt the replication infrastructure from scratch using filter_type 2 and with no NFR clause on the foreign keys supporting the logical record relationships.
What we're now seeing is very clearly a bug in SQL Server.
If the republisher begins synchronising with the publisher and, before it finishes, a client synchronises its anonynous pull subscription with the republisher, then some of the client changes are lost (present on the republisher but never uploaded to the publisher)
Here are the messages captured during programmatic synchronisation on the client:
06/06/2007 11:15:32 Initializing
06/06/2007 11:15:32 Connecting to Publisher 'TT-SQL1\SQL2005'
06/06/2007 11:15:32 Retrieving publication information
06/06/2007 11:15:32 Retrieving subscription information.
06/06/2007 11:15:32 Uploading data changes to the Publisher
06/06/2007 11:15:33 Enumerating deletes in all articles
06/06/2007 11:15:33 Enumerating inserts and updates in article 'Team_Session'
06/06/2007 11:15:33 Uploaded 1 change(s) in 'Outing' (1 update): 1 total
06/06/2007 11:15:33 Uploaded 1 change(s) in 'Stint' (1 update): 1 total
06/06/2007 11:15:33 Uploaded 1 change(s) in 'Lap' (1 insert): 1 total
06/06/2007 11:15:33 Downloading data changes to the Subscriber
06/06/2007 11:15:34 Merge completed after processing 3 data change(s) (1 insert(s), 2 update(s), 0 delete(s), 0 conflict(s)).
06/06/2007 11:15:34 Synchronisation complete
06/06/2007 11:15:59 Initializing
06/06/2007 11:15:59 Connecting to Publisher 'TT-SQL1\SQL2005'
06/06/2007 11:15:59 Retrieving publication information
06/06/2007 11:15:59 Retrieving subscription information.
06/06/2007 11:16:00 Uploading data changes to the Publisher
06/06/2007 11:16:01 Enumerating deletes in all articles
06/06/2007 11:16:01 Enumerating inserts and updates in article 'Team_Event'
06/06/2007 11:16:01 Enumerating inserts and updates in article 'Team_Session'
06/06/2007 11:16:01 Uploaded 1 change(s) in 'EventTyreset' (1 update): 1 total
06/06/2007 11:16:01 Uploaded 1 change(s) in 'Outing' (1 insert): 1 total
06/06/2007 11:16:01 Uploaded 27 change(s) in 'Outing_AeroComponent' (27 inserts): 27 total
06/06/2007 11:16:01 Uploaded 8 change(s) in 'Outing_CofGComponent' (8 inserts): 8 total
06/06/2007 11:16:01 Uploaded 1 change(s) in 'Stint' (1 insert): 1 total
06/06/2007 11:16:01 Uploaded 16 change(s) in 'Outing_BallastComponent' (16 inserts): 16 total
06/06/2007 11:16:01 Downloading data changes to the Subscriber
06/06/2007 11:16:02 Merge completed after processing 54 data change(s) (53 insert(s), 1 update(s), 0 delete(s), 0 conflict(s)).
06/06/2007 11:16:02 Synchronisation complete
And here's what happened on the republisher (in reverse chronological order):
No data needed to be merged.
06/06/2007 11:19:54
No data needed to be merged.
06/06/2007 11:19:41
No data needed to be merged.
06/06/2007 11:18:30
No data needed to be merged.
06/06/2007 11:18:18
Uploaded 1 change(s) in 'EventTyreset' (1 update): 11 total
06/06/2007 11:18:17
Uploading data changes to the Publisher
06/06/2007 11:16:48
No data needed to be merged.
06/06/2007 11:16:32
Uploaded 1 change(s) in 'Lap' (1 insert): 4 total
06/06/2007 11:16:27
Uploaded 1 change(s) in 'Stint' (1 update): 13 total
06/06/2007 11:16:25
Uploaded 1 change(s) in 'Outing' (1 update): 13 total
06/06/2007 11:16:19
Uploading data changes to the Publisher
06/06/2007 11:14:50
No data needed to be merged.
I am desperate to get a solution to this problem. Are there any post-SP2 hot fixes affecting replication?
Thanks in advance.
|||We are seeing something very similar. Updates from a CE database (SDF file) are coming up to the republisher database, but not getting to the Master database. Results are inconsistent at best. The replication seems to be working properly if a direct insert is done at either the republisher level or the Master level. We have not seen a failure in that scenario. We would also like an answer to the problem.
Subscribe to:
Posts (Atom)