Understanding the error message: “Login failed for user ''. The user is not associated with a trusted SQL Server connection.”

Reference URL 

http://blogs.msdn.com/b/sql_protocols/archive/2008/05/03/understanding-the-error-message-login-failed-for-user-the-user-is-not-associated-with-a-trusted-sql-server-connection.aspx

Please check the Article content:

Understanding the error message: “Login failed for user ''. The user is not associated with a trusted SQL Server connection.”

This exact Login Failed error, with the empty string for the user name, has two unrelated classes of causes, one of which has already been blogged about here:http://blogs.msdn.com/sql_protocols/archive/2005/09/28/474698.aspx.  In addition to an extra space in the connection string, the other class of causes for this error message is an inability to resolve the Windows account trying to connect to SQL Server.  This list is not intended to be exhaustive, but here are several known root causes for this error message. 

1)      If this error message occurs every time in an application using Windows Authentication, and the client and the SQL Server instance are on separate machines, then ensure that the account which is being used to access SQL Server is a domain account.  If the account being used is a local account on the client machine, then this error message will occur because the SQL Server machine and the Domain Controller cannot recognize a local account on a different machine.  The next step for this is to create a domain account, give it the appropriate access rights to SQL Server, and then use that domain account to run the client application.  Note that this case also includes the special accounts “NT AUTHORITY\LOCAL SERVICE” and “NT AUTHORITY\NETWORK SERVICE” trying to connect to a remote SQL Server, when authentication uses NTLM rather than Kerberos.

One very common case where this can occur is when creating web applications with SQL Server and IIS; often, the web page will work during development, then errors occur with this message after deploying the web site. This occurs because the developer’s account has access to SQL Server, but the account IIS runs as does not have access.  To fix this specific problem, refer to this kb article about impersonating a domain user in ASP.NET: http://support.microsoft.com/kb/306158

2)      Similar to above: this error message can appear if the user logging in is a domain account from a different, untrusted domain from the SQL Server’s domain.  The next step for this is either to move the client machine into the same domain as the SQL Server and set it up to use a domain account, or to set up mutual trust between the domains.  Setting up mutual trust is a complicated procedure and should be done with a great deal of care and due security considerations.

3)      This error message can appear immediately after a password change for the user account attempting to login.  This occurs because of caching of the client user’s credentials.  The next step here is to log out the application user with the old password, and re-login with the new password before running the application.

4)      If this error message only appears sporadically in an application using Windows Authentication, it may result because the SQL Server cannot contact the Domain Controller to validate the user.  This may be caused by high network load stressing the hardware, or to a faulty piece of networking equipment.  The next step here is to troubleshoot the network hardware between the SQL Server and the Domain Controller by taking network traces and replacing network hardware as necessary.

5)      This error message can appear consistently for local connections using trusted authentication, when SQL Server’s SPN is not interpreted by SSPI as belonging to the local machine.  This can be caused either by a misconfiguration of DNS, or by a machine having multiple names.  If your machine has multiple names, try to work around the need for multiple names and give it a unique name.  If the machine just has one name, then check your DNS configuration.

Drop Database Script: For Reference

EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'DBName' GO USE [master] GO ALTER DATABASE [DBName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO USE [master] GO DROP DATABASE [DBName] GO

Enbale FileStream in Sql Server 2008 from Sql Server Management Studio

Use following command to enable filestream in Sql Server.

Use Master
Go
/*0 = FILESTREAM disabled
1 = FILESTREAM for TSQL enabled
2 = FILESTREAM for TSQL and WIN32 streaming enabled*/
EXEC sp_configure 'filestream access level', 2
Go
RECONFIGURE
Go

Resolved Creating Database Diagram In Sql Server 2008 Express

While creating database diagram in Sql Server 2008, we may get the following error.

Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects.

We need to following steps to resolve this issue.

In SQL Server Management Studio do the following:

1. Right Click on your database, go to properties
2. Goto the Options Page
3. In the Dropdown at right labeled "Compatibility Level" choose "SQL Server 2005(90)"
4. Goto the Files Page
5. Enter "sa" in the owner textbox.
6. press OK

Steps 2 and 3 are optional. With out these steps also I was able to create DB diagram in Sql Server 2008.

Alternate to Dynamic Sql in Sql Server

Following in the Example having an alternate to Dynamic Sql in Sql Server
----------------------------------
create table testtable

( col1 int, col2 int)

insert into testtable(col1, col2)
Values
(10,20),
(30,40),
(11, 15),
(17, 16)
----------------------
Show 1
-----------------------
Declare @col1 int
Declare @col2 int

Set @col1 = 15
SET @col2 = null

Select * from testtable
Where (col1 > @col1 OR @col1 IS NULL)
AND (col2 > @col2 OR @col2 IS NULL)

--------------------------------
Show 2
--------------
Declare @col11 int
Declare @col12 int

SET @col11 = 10
set @col12 = 30
Select * from testtable
Where (col1 = @col11 OR @col11 IS NULL)
or (col1 = @col12 OR @col12 IS NULL)
---------------------------------
Drop table testtable
------------------------

Also you can check the following article from the code project by John P Harris, which have different ways of avoiding Dynamic Sql such as

* Using COALESCE
* Using ISNULL
* Using CASE
* Alternative

Implementing Dynamic WHERE-Clause in Static SQL

Installing Sql Server 2008 Express Management Studio in Windows 7

Ooops!!! This was tough part for me. After lot of surfing on this topic, i found a way to do this.
1. Uninstall the Sql Server Express 2005.
First uninstall the Sql Server 2005 from the Control Panel options
Then remove the "HKLM\Software\Wow6432Node\Microsoft\Microsoft SQL Server\90" key from the registry.
Reference :
Can't Uninstall SQL Server 2005 Express Tools
sql-server-2008-rc0-install-sql2005ssmsexpressfacet

2. Install the Sql Server 2008 Express Management Studio as given in this Article

Following are some articles those may help you to resolve this:
Can't Install Microsoft SQL Server 2008 Management Studio Express

-- In this article, it is suggested that
i. Install Sql Server Management Studio first and then Install Sql Server Express 2008
ii. Else use the "Sql Server Express with Tools" to Install.

Cheers...