Authentication Problem

Hy guys,

I'm really out of the box, and totally have no idea what else i can do or try.
Weird sh*t is going on with my Sql server management studio.

Situation of the problem:

At work we have different servers. Servernames are (sv01, sv02, sv03, ...)
On sv04 is running a complete SQL Server Enterprise edition 2005. We are all logged in on our domain, and connect to the SQL Database Engine on sv04, using Windows Authentication, using the Sql server management studio.

This worked fine, until a few days ago.
Now, when i try to connect with "Windows Authentication", (and yes, the user i'm logging in with has the correct permissions and/or server rights on the domain and on the database).

After logging in while using windows authentication, i get the following error.
<Click here for screenshot>

I already tried:
*) clearing all my temporary files, cookies, cache.
*) Reboot
*) System restore to an earlier point where i was'nt having the problem
*) Searched in registry for "Testuser1" (found nothing)
*) Completely removed everything from Microsoft SQL (Control panel > add/remove programs > ...)

Nothing helped...
Anyone with a solution?

Thx 4 the one who helps me out !

Greetzz Hephie

Hi,

I assume HQ is your domain name.

Can you contact your network administrator and find out if someone has added the user "testuser1" and if it is not needed, can it be removed.

TimDickHarry.

|||hi,

thanks for the fast reply.
HQ is indeed the domain name.

Testuser1 is indeed also a domain name user. But it was there before, and it has always been there. No problems before.

The user can not be deleted, because it's a domain user for testing and developing our applications.

Testuser1 has no rights on the database, but it's not used to connect to the database.
But it think we have to look in another direction. In some way when we choose "Windows authentication" in the sql server management studio, he takes his own way and uses another user to connect to the database.

Possible causes might be, a problem with caching or a stored file somehwere that remembers a login or ... But like i said before, i already removed all of those temp/cache/cookies files.

Anyone else?

|||

In your screenshot, Thomasg is a SQL login, and it is NOT being used -Windows Authentication is being used, and Windows is attempting to log in as [Testuser1].

Are you certain that you are not logged into your local machine as 'testuser1'?

What happens when you completely log out of Windows, and then log back in?

|||Hy,

Yes i'm 100% sure that i'm NOT logged in as testuser1.
In my first post you can see I already try to reboot, re?nstall the complete SQL things..., etc...

Logging out and logging back in didn't solve the problem neither.

Greetzz

Authentication in Management Studio

Hi,

I'm attempting to connect to a SQL Server 2005 instance on a Windows 2003 server remotely with Management Studio on a Windows XP box. SQL Server is set to use Windows Authentication.

If I select Windows Authentication as the authentication method in Management Studio, the 'user name' field is greyed-out and locked to my current XP user name, which is no good for connecting to the server.

Why is this happening and how can I change the user name and password to that of an actual account on the server?

TIA,

CraigyBoop

You can′t switch that for integrated security, as "integrated" security uses the logged on user. The field Username is only valid and accessible if you choose SQL Server Authentication. There are way to impersonate yourself with a Windows credential, but AFAIK these are only programmatically.

HTH, Jens Suessmeyer.|||The account name is picked up from what you have used to login to Windows. This is the idea about "trusted connections", SQL Server trusts that windows has authenticated the login. Start SSMS in another windows account (Windows "Run As" feature) if you want to authenticate differently. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ wrote in message news:67a6dbd1-6f2c-4fd9-93a9-c93d090ea724@.discussions.microsoft.com...
> Hi, >
> I'm attempting to connect to a SQL Server 2005 instance on a Windows
> 2003 server remotely with Management Studio on a Windows XP box. SQL
> Server is set to use Windows Authentication. >
> If I select Windows Authentication as the authentication method in
> Management Studio, the 'user name' field is greyed-out and locked to my
> current XP user name, which is no good for connecting to the server. >
> Why is this happening and how can I change the user name and password to
> that of an actual account on the server? >
> TIA, >
> CraigyBoop >
>|||

I don't understand why that counts as a "Trusted Connection". What's untrusted about being able to log onto a SQL Server with a different Windows account from your current one. Presumably SQL Server can authenticate your Windows credentials if you are using credentials within the same domain?

I too am frustrated by not being able to connect as a different user. Any chance you can explain the reasoning behind this?

Thanks,

Colin

|||I agree this is lame (to use a technical term). If you rely on the client for security, which is what this is doing, you're not secure.
When I'm working remotely I have to use Aqua Data Studio to connect to our db so I can work. The ironic thing about that even though is it's written in Java and a totally non-native client app, it supports Intelli-sense, and will prompt for network credentials. Of course it's java-app so it looks all crappy and acts weird or else I might consider using it full time.
The run-as idea is not bad. I'll give that a try next time.
|||Hi,

actually this is not right, there is not just passed a username in combination with the domain, the user is authenticated and the user token is passed to the Server, thats a different thing than just the user name. So SQL Server trusts the logon server not the client that the user was validated and authenticated.

HTH, Jens K. Suessmeyer

http://www.sqlserver2005.de

Authentication in Management Studio

Hi,

I'm attempting to connect to a SQL Server 2005 instance on a Windows 2003 server remotely with Management Studio on a Windows XP box. SQL Server is set to use Windows Authentication.

If I select Windows Authentication as the authentication method in Management Studio, the 'user name' field is greyed-out and locked to my current XP user name, which is no good for connecting to the server.

Why is this happening and how can I change the user name and password to that of an actual account on the server?

TIA,

CraigyBoop

You can′t switch that for integrated security, as "integrated" security uses the logged on user. The field Username is only valid and accessible if you choose SQL Server Authentication. There are way to impersonate yourself with a Windows credential, but AFAIK these are only programmatically.

HTH, Jens Suessmeyer.|||The account name is picked up from what you have used to login to Windows. This is the idea about "trusted connections", SQL Server trusts that windows has authenticated the login. Start SSMS in another windows account (Windows "Run As" feature) if you want to authenticate differently. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ wrote in message news:67a6dbd1-6f2c-4fd9-93a9-c93d090ea724@.discussions.microsoft.com...
> Hi, >
> I'm attempting to connect to a SQL Server 2005 instance on a Windows
> 2003 server remotely with Management Studio on a Windows XP box. SQL
> Server is set to use Windows Authentication. >
> If I select Windows Authentication as the authentication method in
> Management Studio, the 'user name' field is greyed-out and locked to my
> current XP user name, which is no good for connecting to the server. >
> Why is this happening and how can I change the user name and password to
> that of an actual account on the server? >
> TIA, >
> CraigyBoop >
>|||

I don't understand why that counts as a "Trusted Connection". What's untrusted about being able to log onto a SQL Server with a different Windows account from your current one. Presumably SQL Server can authenticate your Windows credentials if you are using credentials within the same domain?

I too am frustrated by not being able to connect as a different user. Any chance you can explain the reasoning behind this?

Thanks,

Colin

|||I agree this is lame (to use a technical term). If you rely on the client for security, which is what this is doing, you're not secure.
When I'm working remotely I have to use Aqua Data Studio to connect to our db so I can work. The ironic thing about that even though is it's written in Java and a totally non-native client app, it supports Intelli-sense, and will prompt for network credentials. Of course it's java-app so it looks all crappy and acts weird or else I might consider using it full time.
The run-as idea is not bad. I'll give that a try next time.
|||Hi,

actually this is not right, there is not just passed a username in combination with the domain, the user is authenticated and the user token is passed to the Server, thats a different thing than just the user name. So SQL Server trusts the logon server not the client that the user was validated and authenticated.

HTH, Jens K. Suessmeyer

http://www.sqlserver2005.de
sql

Authentication in Management Studio

Hi,

I'm attempting to connect to a SQL Server 2005 instance on a Windows 2003 server remotely with Management Studio on a Windows XP box. SQL Server is set to use Windows Authentication.

If I select Windows Authentication as the authentication method in Management Studio, the 'user name' field is greyed-out and locked to my current XP user name, which is no good for connecting to the server.

Why is this happening and how can I change the user name and password to that of an actual account on the server?

TIA,

CraigyBoop

You can′t switch that for integrated security, as "integrated" security uses the logged on user. The field Username is only valid and accessible if you choose SQL Server Authentication. There are way to impersonate yourself with a Windows credential, but AFAIK these are only programmatically.

HTH, Jens Suessmeyer.|||The account name is picked up from what you have used to login to Windows. This is the idea about

"trusted connections", SQL Server trusts that windows has authenticated the login. Start SSMS in

another windows account (Windows "Run As" feature) if you want to authenticate differently.

--

Tibor Karaszi, SQL Server MVP

http://www.karaszi.com/sqlserver/default.asp

http://www.solidqualitylearning.com/

Blog: http://solidqualitylearning.com/blogs/tibor/

wrote in message

news:67a6dbd1-6f2c-4fd9-93a9-c93d090ea724@.discussions.microsoft.com...

> Hi,

>

> I'm attempting to connect to a SQL Server 2005 instance on a Windows

> 2003 server remotely with Management Studio on a Windows XP box. SQL

> Server is set to use Windows Authentication.

>

> If I select Windows Authentication as the authentication method in

> Management Studio, the 'user name' field is greyed-out and locked to my

> current XP user name, which is no good for connecting to the server.

>

> Why is this happening and how can I change the user name and password to

> that of an actual account on the server?

>

> TIA,

>

> CraigyBoop

>

>|||

I don't understand why that counts as a "Trusted Connection". What's untrusted about being able to log onto a SQL Server with a different Windows account from your current one. Presumably SQL Server can authenticate your Windows credentials if you are using credentials within the same domain?

I too am frustrated by not being able to connect as a different user. Any chance you can explain the reasoning behind this?

Thanks,

Colin

|||I agree this is lame (to use a technical term). If you rely on the client for security, which is what this is doing, you're not secure.
When I'm working remotely I have to use Aqua Data Studio to connect to our db so I can work. The ironic thing about that even though is it's written in Java and a totally non-native client app, it supports Intelli-sense, and will prompt for network credentials. Of course it's java-app so it looks all crappy and acts weird or else I might consider using it full time.
The run-as idea is not bad. I'll give that a try next time.|||Hi,

actually this is not right, there is not just passed a username in combination with the domain, the user is authenticated and the user token is passed to the Server, thats a different thing than just the user name. So SQL Server trusts the logon server not the client that the user was validated and authenticated.

HTH, Jens K. Suessmeyer

http://www.sqlserver2005.de

Attching A Databse To the SQL Server 2000

I am using " Microsoft.SqlServer.Management.Smo". I am trying to attach a database using the AttachDatabse method in it. but it is not working for me.

I am pasting my code as follows...tell me the problem or the right way to do it...

SqlConnection Connection =newSqlConnection("Data Source=SHEBZ; User ID=sa; Password=sa");

Microsoft.SqlServer.Management.Common.ServerConnection con =new Microsoft.SqlServer.Management.Common.ServerConnection(Connection);Server ser =newServer("SHEBZ");

ser.ConnectionContext.Connect();

System.Collections.Specialized.StringCollection File =new System.Collections.Specialized.StringCollection();

File.Add(@."D:\Project-Parts\MasterPageOffice\Uploads\Db3");

try

{

ser.AttachDatabase("Db3", File);

}

catch (Exception ex)

{

Label1.Text ="ERROR: " + ex.Message.ToString();

}

Hi,

What you are using is Server.AttachDatabase method, try to follow the sample below.

Datastr = "C:\Program Files\Microsoft SQL Server"Datastr = datastr + "\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf"Logstr = "C:\Program Files\Microsoft SQL Server"Logstr = datastr + "\MSSQL.1\MSSQL\Data\AdventureWorks_Log.ldf"

StringCollection sc;sc = new StringCollection();sc.Add(datastr);sc.Add(logstr);srv.AttachDatabase("AdventureWorks", sc, owner, AttachOptions.None);

Thanks.

|||

I know this code...it is for detaching and attaching the databse to a SQL Server 2005 or high..... if you try to use this code for SQL Server 2000 you will get the same exception that this property or method can be used with Sql Server 2005 or high... i want a solution for 2000...

Attaching the log file and the mdf files of the database.

When I try to attach both the log file and mdf files

I got the following error message.

TITLE: Microsoft SQL Server Management Studio

Attach database failed for Server 'FCIT-GFOX082'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

The header for file 'C:\Documents and Settings\Administrator\My Documents\Microsoft Press\AS2005SBS\Setup\Query\log_attach_databases.txt' is not a valid database file header. The FILE SIZE property is incorrect. (Microsoft SQL Server, Error: 5172)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=08.00.0194&EvtSrc=MSSQLServer&EvtID=5172&LinkId=20476

BUTTONS:

OK

Could someone help me on how to go about this.

Regards,

Ronald

Maybe you need to take a step back and tell us what you were trying to accomplish. SSAS does not use mdf and log files. And the file in the error message you posted is a .txt file.

SSAS either uses a .abf format for back up and restore, or an xmla script to create an empty database.

|||

I am trying to attach the SSAS Step by Step database that comes with the book Microsoft SQL SERVER 2005 Analysis SErvices Step by Step.

When I try it out, I get error messages.

Regards,

Ronaldl

Attaching DB's to Sql Server Management (User Instantances) Error

I have been working on a website in Visual Studio 2005 Pro using the MS SQL Server that comes with VS. I have tried Attaching those MDF files in Visual Studio to the SQL Server Express Management but it wont let me, get an error::

Error Number: 5133
Severity: 16
State: 1
Line Number: 1

TITLE: Microsoft SQL Server Management Studio Express

Attach database failed for Server 'WYATT-PC\SQLEXPRESS'. (Microsoft.SqlServer.Express.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)

Directory lookup for the file "C:\Users\Wyatt\Documents\Visual Studio 2005\WebSites\AdultUnderWorld\App_Data\aspnetdb.mdf" failed with the operating system error 5(Access is denied.). (Microsoft SQL Server, Error: 5133)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=5133&LinkId=20476

BUTTONS:

OK

Here is the info for the Web.config Connection Strings::

<connectionStrings>

<add name="Personal" connectionString="Data Source=.\SQLExpress;Integrated Security=True;User Instance=True;AttachDBFilename=|DataDirectory|Personal.mdf" providerName="System.Data.SqlClient" />

<remove name="LocalSqlServer"/>

<add name="LocalSqlServer" connectionString="Data Source=.\SQLExpress;Integrated Security=True;User Instance=True;AttachDBFilename=|DataDirectory|aspnetdb.mdf" />

</connectionStrings>

I want to attach the above databases into the SQL Server Management Studio,so how can i go about doing this?

You don't attach this database using Management Studio, it is being used by a User Instance and automatically attaches at runtime of the application. By default, Management Studio works against the parent instance of SQL Express, not your User Instance, check out the blog post that documents how to connect to your User Instance with Management Studio. (FYI - This is listed in the FAQ at the top of this forum, always a good place to check every once in a while to see if your question is answered there.)

Mike

attaching a database to sql express

I'm trying to attach a database to sql express with the following code from the management tool. I keep getting an error message. An someone explain what I'm doing wrong.

Msg 102, Level 15, State 1, Line 9

Incorrect syntax near '<'.

IF NOT EXISTS(

SELECT *

FROM sys.databases

WHERE name = N'<northwind.mdb, I4V0Y6\SQLEXPRESS, northwind>'

)

CREATE DATABASE <database_name, sysname, your_database_name>

ON PRIMARY (FILENAME = '<c:\my documents\my webs\myweb3,,C:\Program files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\northwind.MDF>')

FOR ATTACH

GO

Ira

You have to substitute the value in the <> signs with your actual values. SO it should evaluate to something like:

CREATE DATABASE Archive
ON (FILENAME = 'C:\Program files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\northwind.MDF')
FOR ATTACH

The BOL has more samples about that.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Attached Northwind to SQL Express but no data in tables

I installed Northwind sample database by running the install script within server management sudio express and the database and tables show up, but there are no records. Once executed, it says it was successful.

Am I missing something?

The data is there. I had built some code to extract the customer data to a datagrid and it was only pulling the column headers and no data. I opened the managment studio and asked it to open a table and the records were there. Sorry for the post.