Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'

I get the following error message when I select a report:

An error has occurred during report processing.

Cannot create a connection to data source 'PSS5'.

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

My Data source is set to Windows Integrated Security when I get this error message. If I change my data source to be "Credential supplied by the user running the report", the report generates fine. I am running SQL 2005 SP2. I have SQL reporting services installed on different server than my database services. Both servers are W2K3 R2 x64.

My IIS website and virtual directories are set up for Integrated Windows Authentication. My IIS application pool is set up to use an AD service account. I have followed the step/procedures outlined at: http://www.microsoft.com/technet/prodtechnol/windowsserver2003/technologies/security/tkerbdel.mspx

http://blogs.msdn.com/sql_protocols/archive/2005/10/12/479871.aspx
http://blogs.msdn.com/sql_protocols/archive/2006/08/10/694657.aspx

but to no avail. I am sure that I am missing something, but I can't figure out what. I would like to use Integrated Authentication, any ideas?

You are using allow anonymous in IIS. This means that your ReportViewer control is not passing the users credentials to the report server. If you want to use "allow anonymous" you will need to pass valid credentials to the report server. This can be done in your code that calls the Report Viewer control/Report Server.|||

Hi,

see more information about passing credentials programmatically here:

http://blogs.msdn.com/bimusings/archive/2005/11/05/489423.aspx

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

I don't think that the problem is related to ReportViewer or IIS configuration (Since the credantial information is passed to the datasource and denied). When u select Windows Intedrated Security for report options, the credantials of the user that initiates the Reporting Services at every start up (by default this user is: 'NT AUTHORITY\ANONYMOUS LOGON') are sent to the datasource (And if this user is not authorized in the target database you get the following error).

You can authorize the Reporting Services user in the target database ('NT AUTHORITY\ANONYMOUS LOGON' exists within your SQL Server Machines Users.

or

You can change the Reporting Services' user to a domain user that both computers can authanticate.

Administrative Tools -> Services. (Find 'SQL Server Reporting Services (MSSQLSERVER)' change user from LOG ON tab.)

I hope this helps.

|||Thanks for the replies, but they didn't seem to help. To clear up a few questions, this report was developed in Visual Studio 2005 and it works fine in it. The report renders ok if I launch in on the server, but not anywhere else. My Reporting Services' user is an AD account that both my servers can authenicate against, and this user does have access to the DB. My Website and virtual directories have "allow anonymous access" unchecked.|||I decided to try one more thing. I went into the web.config file and changed the <identity impersonate="false"/> and that fixed it for me. Thanks for all the replies.|||

Note:

The above fix will force IIS to use the Application pool account to connect to SQL on the remote server.

This is still not the permanent solution that I am looking for, I believe my problem is a kerberos issue at this point. Any tips/hints/suggestions on troubleshooting this problem would be helpful.

sql

Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server conn

Currently, i'm using Visual Web Developer 2005 to create my web site.

Once i connect to database, it's comes out this error for me "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection."
I'm try to connect the database in microsoft server 2000.

How can I solve it?Can your mention what connection string you write in your code

Login failed for user '(null)'

I'm getting the following error:
An error has occurred during report processing.
Cannot create a connection to data source 'MyReports-ReportServer-PHX'.
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
This is only for data sources that use integrated authenticaiton. This is
on a fresh install of SQL Server 2005 and Reporting Services 2005 using
Visual Studio 2005. Per several articles/disuccsions, I've configured IIS
directory secruity to NOT allow anonymous access and to allow the Integrated
Windows Authentication exactly as I have on my other database server. This
is Windows Server 2003 IIS 6.0 and my other server is Windows 2000, IIS 5.0.
Any help would be GREATLY appreciated. Does this have something to do with
the IIS_WPG group not having correct permissions? The users do have the
correct permissions in my environment.Here's the stack trace as well from the reporting services error log...lots
of errors about the connection pool?
-- End of inner exception stack trace --
w3wp!webserver!1!01/24/2006-15:04:38:: e ERROR: Reporting Services error
Microsoft.ReportingServices.Diagnostics.Utilities.RSException: An error has
occurred during report processing. -->
Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An
error has occurred during report processing. -->
Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
Cannot create a connection to data source 'MyReports-ReportServer-PHX'. -->
System.Data.SqlClient.SqlException: Login failed for user '(null)'. Reason:
Not associated with a trusted SQL Server connection.
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, Boolean breakConnection)
at
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet
bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean
enlistOK)
at
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection
owningObject, SqlConnectionString connectionOptions, String newPassword,
Boolean redirectedUserInstance)
at
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity
identity, SqlConnectionString connectionOptions, Object providerInfo, String
newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions
options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection
owningConnection)
at
System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection
owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection
owningObject)
at
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection
owningConnection)
at
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection
outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at Microsoft.ReportingServices.DataExtensions.SqlConnectionWrapper.Open()
at
Microsoft.ReportingServices.ReportProcessing.ReportProcessing.ReportRuntimeDataSourceNode.OpenConnection(DataSource
dataSourceObj, ReportProcessingContext pc)
-- End of inner exception stack trace --
at
Microsoft.ReportingServices.ReportProcessing.ReportProcessing.ReportRuntimeDataSourceNode.Process()
at
Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RuntimeDataSourceNode.ProcessConcurrent(Object threadSet)
-- End of inner exception stack trace --
at
Microsoft.ReportingServices.ReportProcessing.ReportProcessing.ProcessingContext.AbortHelper.ThrowAbortException(Int32 reportUniqueName)
at
Microsoft.ReportingServices.ReportProcessing.ReportProcessing.ProcessingContext.CheckAndThrowIfAborted()
at
Microsoft.ReportingServices.ReportProcessing.ReportProcessing.Merge.Process(ParameterInfoCollection parameters, Boolean mergeTran)
at
Microsoft.ReportingServices.ReportProcessing.ReportProcessing.ProcessReport(Report report, ProcessingContext pc, ProcessingContext context)
at
Microsoft.ReportingServices.ReportProcessing.ReportProcessing.ProcessReport(Report
report, ProcessingContext pc, Boolean snapshotProcessing, Boolean
processWithCachedData, GetReportChunk getChunkCallback, ErrorContext
errorContext, DateTime executionTime, CreateReportChunk cacheDataCallback,
ProcessingContext& context)
at
Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RenderReport(IRenderingExtension
renderer, DateTime executionTimeStamp, GetReportChunk
getCompiledDefinitionCallback, ProcessingContext pc, RenderingContext rc,
CreateReportChunk cacheDataCallback, Boolean& dataCached)
at
Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RenderReport(DateTime
executionTimeStamp, GetReportChunk getCompiledDefinitionCallback,
ProcessingContext pc, RenderingContext rc)
at
Microsoft.ReportingServices.Library.RSService.RenderAsLive(CatalogItemContext
reportContext, ItemProperties properties, ParameterInfoCollection
effectiveParameters, Guid reportId, ClientRequest session, String
description, ReportSnapshot intermediateSnapshot, DataSourceInfoCollection
thisReportDataSources, Boolean cachingRequested, Warning[]& warnings,
ReportSnapshot& resultSnapshotData, DateTime& executionDateTime,
RuntimeDataSourceInfoCollection& alldataSources, UserProfileState&
usedUserProfile)
at
Microsoft.ReportingServices.Library.RSService.RenderAsLiveOrSnapshot(CatalogItemContext
reportContext, ClientRequest session, Warning[]& warnings,
ParameterInfoCollection& effectiveParameters)
at
Microsoft.ReportingServices.Library.RSService.RenderFirst(CatalogItemContext
reportContext, ClientRequest session, Warning[]& warnings,
ParameterInfoCollection& effectiveParameters, String[]& secondaryStreamNames)
at Microsoft.ReportingServices.Library.RenderFirstCancelableStep.Execute()
at
Microsoft.ReportingServices.Diagnostics.CancelablePhaseBase.ExecuteWrapper()
-- End of inner exception stack trace --
at
Microsoft.ReportingServices.Diagnostics.CancelablePhaseBase.ExecuteWrapper()
at
Microsoft.ReportingServices.Library.RenderFirstCancelableStep.RenderFirst(RSService
rs, CatalogItemContext reportContext, ClientRequest session, JobType type,
Warning[]& warnings, ParameterInfoCollection& effectiveParameters, String[]&
secondaryStreamNames)
at
Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderReport(HttpResponseStreamFactory streamFactory)
at
Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.DoStreamedOperation(StreamedOperation operation)
at
Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderItem(ItemType itemType)
at
Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderPageContent()
at
Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderPage()

Login failed for user "David". Reason: Not associated with a trusted SQL Server connec

I am very new to SQL Server Express, so I watched the video on how to create permissions in SQLSE. I did what I seen, and I still cannot connect to the specifed database.

I did create a login account in the main part using SQL Auth., then I create a login account in the database, and set its role as dbowner.

After all that I disconnected the object explorer and tried to use the account that I just made and it didnt work, I also went to VBE and tried to connect from within the database explorer and I got the same error above.

I can go to a different pc and try to connect with the same result.

I did watch the video again to make sure that I didnt miss up and what I did was right by the video.

I did try to set another name and user account with no prevail.

Where did I miss up?

Davids Learning

I did a reboot, and tried to login again and got a different error.

A connection was established, but an error occured etc.,provider -shared memory provider,error;0(no process is on the other end of the pipe)sql error 233

I tried to login again and it error back to the previous post any ideals?

Davids Learning

|||

Turn off simple file sharing

make sure comb of windows and sql auth mode

reboot not restart service

Finally after 8 hours of trying different things, watching a video, reading blogs, reading books,posting and having no one to respond.

Proud to say

I took care of it myself!!!!!!!!!!!!!!!!!!!!

Davids Learning

When I get as smart as some of you ,I guarantie I help someone like me who needs it

|||

Your posts were made in a 3 hour interval. How did you manage to spend 8 hours of trying things during that period?

Thanks
Laurentiu

|||

I shouldnt have to explain my schedule, but I will since you asked.

I acutally started on this at 3:30 am yesterday morning.

What you dont know that gooes on inbehind the post gotcha.

I worked with this on and off during the morning, ate lunch, worked on it again in the afternoon on and off, made a few post because I was getting stumped, fiqured out my problem, went home at 7:00pm.

Davids Learning

|||David I'm having the same problem can you share your resolution to this problem with me. Thanks|||

mjven, can you tell me a little about what you have and what you have tried?

not trying to push you off, but reading what others have tried and comparing them to your situation is how to learn.

I havent been able to work on my project for a while due to another project, so forgive me should I steer you wrong, I am still learning too.

I will tell you this, check in your connection string, that was where I had my biggest problem, */Sql should be replace with the name of your servers name I believe like mjvenpc/SQL.

I will check back in the morning should you need an opinion.

Hang in there

Davids Learning

Login failed for user "David". Reason: Not associated with a trusted SQL Server co

I am very new to SQL Server Express, so I watched the video on how to create permissions in SQLSE. I did what I seen, and I still cannot connect to the specifed database.

I did create a login account in the main part using SQL Auth., then I create a login account in the database, and set its role as dbowner.

After all that I disconnected the object explorer and tried to use the account that I just made and it didnt work, I also went to VBE and tried to connect from within the database explorer and I got the same error above.

I can go to a different pc and try to connect with the same result.

I did watch the video again to make sure that I didnt miss up and what I did was right by the video.

I did try to set another name and user account with no prevail.

Where did I miss up?

Davids Learning

I did a reboot, and tried to login again and got a different error.

A connection was established, but an error occured etc.,provider -shared memory provider,error;0(no process is on the other end of the pipe)sql error 233

I tried to login again and it error back to the previous post any ideals?

Davids Learning

|||

Turn off simple file sharing

make sure comb of windows and sql auth mode

reboot not restart service

Finally after 8 hours of trying different things, watching a video, reading blogs, reading books,posting and having no one to respond.

Proud to say

I took care of it myself!!!!!!!!!!!!!!!!!!!!

Davids Learning

When I get as smart as some of you ,I guarantie I help someone like me who needs it

|||

Your posts were made in a 3 hour interval. How did you manage to spend 8 hours of trying things during that period?

Thanks
Laurentiu

|||

I shouldnt have to explain my schedule, but I will since you asked.

I acutally started on this at 3:30 am yesterday morning.

What you dont know that gooes on inbehind the post gotcha.

I worked with this on and off during the morning, ate lunch, worked on it again in the afternoon on and off, made a few post because I was getting stumped, fiqured out my problem, went home at 7:00pm.

Davids Learning

|||David I'm having the same problem can you share your resolution to this problem with me. Thanks|||

mjven, can you tell me a little about what you have and what you have tried?

not trying to push you off, but reading what others have tried and comparing them to your situation is how to learn.

I havent been able to work on my project for a while due to another project, so forgive me should I steer you wrong, I am still learning too.

I will tell you this, check in your connection string, that was where I had my biggest problem, */Sql should be replace with the name of your servers name I believe like mjvenpc/SQL.

I will check back in the morning should you need an opinion.

Hang in there

Davids Learning

Login failed for user "David". Reason: Not associated with a trusted SQL Server co

I am very new to SQL Server Express, so I watched the video on how to create permissions in SQLSE. I did what I seen, and I still cannot connect to the specifed database.

I did create a login account in the main part using SQL Auth., then I create a login account in the database, and set its role as dbowner.

After all that I disconnected the object explorer and tried to use the account that I just made and it didnt work, I also went to VBE and tried to connect from within the database explorer and I got the same error above.

I can go to a different pc and try to connect with the same result.

I did watch the video again to make sure that I didnt miss up and what I did was right by the video.

I did try to set another name and user account with no prevail.

Where did I miss up?

Davids Learning

I did a reboot, and tried to login again and got a different error.

A connection was established, but an error occured etc.,provider -shared memory provider,error;0(no process is on the other end of the pipe)sql error 233

I tried to login again and it error back to the previous post any ideals?

Davids Learning

|||

Turn off simple file sharing

make sure comb of windows and sql auth mode

reboot not restart service

Finally after 8 hours of trying different things, watching a video, reading blogs, reading books,posting and having no one to respond.

Proud to say

I took care of it myself!!!!!!!!!!!!!!!!!!!!

Davids Learning

When I get as smart as some of you ,I guarantie I help someone like me who needs it

|||

Your posts were made in a 3 hour interval. How did you manage to spend 8 hours of trying things during that period?

Thanks
Laurentiu

|||

I shouldnt have to explain my schedule, but I will since you asked.

I acutally started on this at 3:30 am yesterday morning.

What you dont know that gooes on inbehind the post gotcha.

I worked with this on and off during the morning, ate lunch, worked on it again in the afternoon on and off, made a few post because I was getting stumped, fiqured out my problem, went home at 7:00pm.

Davids Learning

|||David I'm having the same problem can you share your resolution to this problem with me. Thanks|||

mjven, can you tell me a little about what you have and what you have tried?

not trying to push you off, but reading what others have tried and comparing them to your situation is how to learn.

I havent been able to work on my project for a while due to another project, so forgive me should I steer you wrong, I am still learning too.

I will tell you this, check in your connection string, that was where I had my biggest problem, */Sql should be replace with the name of your servers name I believe like mjvenpc/SQL.

I will check back in the morning should you need an opinion.

Hang in there

Davids Learning

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

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

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

Login failed for give user id

I tried to create a mining model from a remote SQL 2000 database using local user id of remote server. Whenver I try to process the model I am getting login failed error. But the same model runs fine when I configure datasource using windows credentials.I found out the reason, when I set the impersonation mode to service account/degfault its working. But when I set to specific user name and password, its not working. Can somebody suggest me the possible cause.|||I found answer myself. I just created a local user and a role in analysis service database mapping to local user and granted him appropriate rights. Now I can process the data model under "impersonate particular user mode"

login creation problem

hi, i'm using the last ctp of sql server management studio to create a database to sql express and add a login that give access to that database.
i created the login, assigned the user to the database assignig db_onwer and public, setting the default database, and create the login.
if i browse inside the database i find the news user created, the login seem created fine and linked to the user of the database, but if i try to use the login data to connect to the database i obtain the message

Login failed for user 'sitogs'. The user is not associated with a trusted SQL Server connection. (.Net SqlClient Data Provider)

this happens with both management studio and a my asp.net application.
I followed the procedure i always used with enterprisa manager and sql server.
Im doing something wrong?

> try to use the login data to connect to the database i obtain the
> message >
> Login failed for user 'sitogs'. The user is not associated with a
> trusted SQL Server connection. (.Net SqlClient Data Provider) What does your connection string look like? Are you sure you are trying to use SQL auth and not windows auth? Sounds like you are relying on windows auth. A

|||the connection string is:

workstation id=XPNEW;packet size=4096;user id=sitogs;data source=XPLAP;persist security info=True;password=sitogs

and is working fine with sql server 2000 with sql server authentication, and i have the same problem if i open sql management studio, change to sql server authentication and insert userid and password

|||"Trusted connection" is synonymous with "Windows Authentication". Make sure you've selected SQL Authentication in the connection dialog and not Windows Authentication.

Login box when starting Report Builder

I'm new in SSRS so hopefully this will be an easy question.

I want to create a report model based on a cube. I have created a data source for the cube. Now when I click on Report Builder I get a login box titled "Microsoft Report Server Login".

When I enter the local admin account and password I get:

No report server was found at http://crossdatabase/ReportServer

When I enter that URL in IE I get:

crossdatabase/ReportServer - /

---

zondag 16 september 2007 23:24 <ds> Crossdatabase

Microsoft SQL Server Reporting Services Version 9.00.1399.00 What am I doing wrong? I already found some explanation about adding the Report Builder role so in home I now have:

EditBUILTIN\AdministratorsContent Manager, Report Builder

Anyone got any ideas?
Thanks,Stijn Verrept.

Please review the link below and let us know if this fixes your issue.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1697246&SiteID=1

|||I won't have access to the machine till later on, but this post is about users not seeing the Report Builder button. I'm admin, I see the button but when I click it I get the login box.|||

I tried but still get the same login box after clicking on the Report Builder button.

This is the login box that I get:

http://www.caresolutions.be/login.jpg

|||

According to your log above, you are running SQL Server 2005 RTM (Version 9.00.1399.00 ). You need to apply Service Pack 2 which should get you to Version 9.00.3042.00 and may fix your problem.

|||

This indeed fixed my problem, I now have another one however, but I'll post this in a new thread. Thanks.

Login Authentication for Standard Accounts

We have a couple of vendor applications that create standard accounts.
These accounts have the same format as our domain accounts. For
example, my Windows account would be <domain>\<user_name>. The SQL
standard account would be <user_name>.
What we would like to do is change the login process to validate
against active directory either prior to or instead of checking the
SQL password. Everything else could remain the same.
This would be on SQL 2000 or SQL 2005. Any suggestions or other
insight would be appreciated.RogerT (roger.tompkins@.gmail.com) writes:
> We have a couple of vendor applications that create standard accounts.
> These accounts have the same format as our domain accounts. For
> example, my Windows account would be <domain>\<user_name>. The SQL
> standard account would be <user_name>.
> What we would like to do is change the login process to validate
> against active directory either prior to or instead of checking the
> SQL password. Everything else could remain the same.
> This would be on SQL 2000 or SQL 2005. Any suggestions or other
> insight would be appreciated.
I'm not really sure what you mean here. SQL Server provides to
means of authentication: SQL authentication and Windows authentication.
It sounds from your description that the vendor accounts are for
SQL authentication. But you cannot change a login from being an SQL
login, to be a Windows login. These two types of logins are competely
unconnected.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

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.

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.

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.

Login

Is it poosible to create a user in SQL Server 2000 who has the rights to
update, delete & insert in a database. The user can login in to tha databas
e
through a third party application i.e VB etc but cannot log directly on the
enterprise manager and query analyzer console.
ThankxYes,
S2K Database has Role and User.
You can create users and roles.
You can use built-in Database Role such as db_datawriter.
"Muhammad Bilal"?? ??? ??:

> Is it poosible to create a user in SQL Server 2000 who has the rights to
> update, delete & insert in a database. The user can login in to tha datab
ase
> through a third party application i.e VB etc but cannot log directly on th
e
> enterprise manager and query analyzer console.
> Thankx

Login

I have a database that I designed for my users. Sql is the Engine and access is on the front end. I was wondering if there was a way to create a login screen, also if there was a way to create a filter that I can tie into some queries and reports. In Access you can put [] in the criteria area, type in the value and then you get that record/results. SQl server does not recognize this. What do I do, what would be the equivelant??Access has its own security login system. To enable it, give the Admin user a password. If you want to auto load a welcome screen, create a macro called Autoexec and call your welcome screen from the macro. If you want to use Access [..] facility, use linked tables and do the query in Access. This will work fine for small tables but would be slow on large amounts of data because access will need to bring all the data across the network to query it. For big tables it is better to build a SQL query in access and use a pass-through query to execute the query on the server and send just the results back to Access.

Andy

logical inserted table and after triggers

for some reason my create trigger query fails because sql server cannot resolve the term inserted. It won't even parse without errors. This is what my t-sql looks like

CREATE TRIGGER UserTypeTrig ON GTData.dbo.GTUserType

AFTER Insert, Update

AS

SET NOCOUNT ON

IF EXISTS(SELECT *

FROM GTData.dbo.GTUserType G

JOIN INSERTED I

ON G.TypeID != I.TypeID AND LOWER(G.UserType) = LOWER(I.UserType);

BEGIN;

RAISERROR('cannot insert duplicate userType', 16, 1)

ROLLBACK TRANSACTION

RETURN

END;

appreciate all the help i can get.

Your syntax is not correct. You are using statement terminators in the wrong place. See the modified code:

CREATE TRIGGER UserTypeTrig ON GTData.dbo.GTUserType

AFTER Insert, Update

AS

SET NOCOUNT ON;

IF EXISTS(SELECT *

FROM GTData.dbo.GTUserType G

JOIN INSERTED I

ON G.TypeID != I.TypeID AND LOWER(G.UserType) = LOWER(I.UserType)

)

BEGIN;

RAISERROR('cannot insert duplicate userType', 16, 1);

ROLLBACK TRANSACTION;

RETURN;

END;

|||

Umachandar Jayachandran

Thanks a lot!

logical device already exists?

I want to back the master db to the disk file e:\data\MSSQL\BACKUP\master.BAK.
When I tried to create a new backup device called 'master', I got the error:
Error 15026: Logical device 'master' already exists.
But I've checked several times and don't see master.BAK already exist in e:\data\MSSQL\BACKUP. I had no problem creating the backup devices for other system default databases, like msdb, model, etc.
Anybody know what might be the problem?
Bing
When you create a backup device you are associating a logical name to a
physical file name. In SQL Server there is more than one type of device.
If you look in BOL under sysdevices you can find out about all the different
devices. Anyway "master" already is a device that is associated with the
actual master database. If you want to create a logical backup device to
refer to your master database backup, call it "master_bak", or something.
Hope this helps you understand what a device is. If you really want to see
all the devices you already have assigned you can run the following command
from QA:
select * from master.dbo.sysdevices
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"bing" <bing@.discussions.microsoft.com> wrote in message
news:9AC7B842-64A8-4790-8C39-87957075EE39@.microsoft.com...
> I want to back the master db to the disk file
e:\data\MSSQL\BACKUP\master.BAK.
> When I tried to create a new backup device called 'master', I got the
error:
> Error 15026: Logical device 'master' already exists.
> But I've checked several times and don't see master.BAK already exist in
e:\data\MSSQL\BACKUP. I had no problem creating the backup devices for
other system default databases, like msdb, model, etc.
> Anybody know what might be the problem?
> Bing
|||Hi,
It seems there is an entry in master..sysdevices table.
Execute the below procedure from Query ANalyzer and ensure that you do not
have the same file.
sp_helpdevice
To double check the same by querying :-
select * from master..sysdevices
If you have an entry then you can drop the device by using the below command
(Execute the comand if you need)
sp_dropdevice 'device_name'
Thanks
Hari
MCDBA
Thanks
Hari
MCDBA
"bing" <bing@.discussions.microsoft.com> wrote in message
news:9AC7B842-64A8-4790-8C39-87957075EE39@.microsoft.com...
> I want to back the master db to the disk file
e:\data\MSSQL\BACKUP\master.BAK.
> When I tried to create a new backup device called 'master', I got the
error:
> Error 15026: Logical device 'master' already exists.
> But I've checked several times and don't see master.BAK already exist in
e:\data\MSSQL\BACKUP. I had no problem creating the backup devices for
other system default databases, like msdb, model, etc.
> Anybody know what might be the problem?
> Bing
|||Thanks all for the information. Very helpful.
Bing
"Hari Prasad" wrote:

> Hi,
> It seems there is an entry in master..sysdevices table.
> Execute the below procedure from Query ANalyzer and ensure that you do not
> have the same file.
> sp_helpdevice
> To double check the same by querying :-
> select * from master..sysdevices
> If you have an entry then you can drop the device by using the below command
> (Execute the comand if you need)
> sp_dropdevice 'device_name'
> Thanks
> Hari
> MCDBA
>
> Thanks
> Hari
> MCDBA
>
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:9AC7B842-64A8-4790-8C39-87957075EE39@.microsoft.com...
> e:\data\MSSQL\BACKUP\master.BAK.
> error:
> e:\data\MSSQL\BACKUP. I had no problem creating the backup devices for
> other system default databases, like msdb, model, etc.
>
>