Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 252
  • Last Modified:

Can you connect to sql database 2005 using the computer's windows authentication?

If you have Sql server, and has 2 logins (windows authentication, and sa authentication).
Can you connect to the SQL using the window's authentication of the sql server.
Because it is not working with me.
Thanks
0
Ihab
Asked:
Ihab
  • 9
  • 5
  • 4
  • +3
5 Solutions
 
Harish VargheseProject LeaderCommented:
Yes. What error do you get when you try windows authentication?
0
 
Steve WalesSenior Database AdministratorCommented:
You can but there are a few things that need to be done first.

Someone (the DBA?) needs to create a login for you windows account on the SQL Server.

Then, unless you're granted a server wide role, they will then need to create users in the databases you want to access that are linked to that login (and have appropriate permissions granted).

Here's an article at technet talking about it:  http://technet.microsoft.com/en-us/library/aa337562%28v=sql.90%29.aspx
0
 
IhabAuthor Commented:
Check the message
SQl1.jpg
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Kamal KhaleefaInformation Security SpecialistCommented:
yes you can connect using windows authentication and sql authentication

see this
http://technet.microsoft.com/en-us/library/ms345332.aspx
0
 
IhabAuthor Commented:
When I choose windows authentication. The fields are dimmed, not allowing to enter username and password
0
 
Harish VargheseProject LeaderCommented:
The error does not seem to be specific to login. Are you able to connect to this server from the same machine using sql server authentication? Are you logging in from the server machine itself or a client machine? If it is from a client machine, you need to configure the server for remote access.
0
 
IhabAuthor Commented:
In the server I am logging normal in windows authentication.
From a client :
In Management Studio, on the File menu,

-I Select Database Engine.
-In the Server name box, <computer_name>
-I select windows authentication
-username field and password are dimmed

    Click Connect.
I got the message above in the print screen
0
 
Harish VargheseProject LeaderCommented:
That indicates that your server is not confitured for remote connections (connections from other machines). The link below explains clearly the steps you can follow to enable remote connections.
How to enable remote connections in SQL Server 2008?
The procedure is same for SQL 2005. You need to do this from the server.

-Harish
0
 
IhabAuthor Commented:
After I enable this , I will be able to connect.
0
 
Harish VargheseProject LeaderCommented:
Do you mean that you enabled this and you able to connect now?
Or have you not tried this yet?
0
 
chanderpal singh rathoreMicrosoft Exchange EngineerCommented:
Hi  ihab_h,

Yes, you can login from other machines just after enabling remote login
0
 
IhabAuthor Commented:
I will try it and I will come back to you
0
 
Steve WalesSenior Database AdministratorCommented:
One thing to note is that when you select Windows Authentication, the username and password are always protected.

You can't enter a username and password because the whole idea of Windows Authentication is that it takes your Windows credentials (what you used to login to Windows) and passes them to SQL Server.
0
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
The error message indicates that it is trying to connect with Named Pipes.  you should go into the SQL Server Configuration Manager and check the Protocols section for your instance and verify that Named Pipes is enabled.  By default it is not, but TCP/IP is.

I would try this first.  If in the connection dialog, you click on the Options button on the bottom and choose the Protocol TCP/IP for connecting and then attempt to connect. If it connects or gives a different error, then you can go from there.  If it connects you know that Named Pipes is not configured and you can decide whether you want to configure it.
0
 
IhabAuthor Commented:
So Mr. Stev it will not work, because the username and password for the sql's server is different than mine.is there another way
Thanks
0
 
IhabAuthor Commented:
Can I add user other than sa in the sql?
0
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
You can, but you could try to right click on management studio and click Run As and set the username and password from windows that your app uses and then try to connect with Windows Authentication then it will use the username that you ran the management studio as.
0
 
IhabAuthor Commented:
Mr. dba.
How it will work and the both computers have different names. Even I try to right click the management studio and click run as , it doesnt work.
0
 
Steve WalesSenior Database AdministratorCommented:
In my environment, I have my local credentials on my laptop for my local domain and one of the SQL Servers I manage is in a different domain.

For Windows authentication, I log on to a machine in that domain via Remote Desktop and initiate Management Studio from there so that my connection to the machine is using the windows credentials in that domain.

If  you're all in the same domain, assuming you have permissions to do so you can still setup the instance to accept connections.

Say the instance or app runs as DOMAIN\appuser.
Your user is DOMAIN\ihab_h

You can do this:

USE [master]
GO
CREATE LOGIN [DOMAIN\ihab_h] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
EXEC master..sp_addsrvrolemember @loginame = N'DOMAIN\ihab_h', @rolename = N'serveradmin'
GO

Open in new window


The password becomes irrelevant.

This establishes your account as a serveradmin on that server with full rights to that server.

You could run that as 'sa' to establish the rights for yourself (assuming you're the DBA and you are allowed to have those permission levels on that server).

You should then be able to launch SSMS, choose Windows authentication and it will pass your windows authentication token to SQL Server and you're away.
0
 
IhabAuthor Commented:
This senario for one of my client , I know it is not the best practice.
The SQL server it is not in the domain, it uses the local administrator authentication, and I dont know the sa password and I dont want to change because it may affects another app.

I want to log in to the sql from my machine, Do you mean I run the SQL Management studio and write and computer name of the sql \administrator and the password?
0
 
DBAduck - Ben MillerPrincipal ConsultantCommented:
No you cannot do that.  You can only use pass through authentication when it is like that. That is what a domain buys you.

So you would need to be on the machine logged in as the admin account.
0
 
Steve WalesSenior Database AdministratorCommented:
If you're in different domains (sounds like you have the same situation I do) then I don't think you can use Windows Authentication.  You'd need to get a SQL Server account created (which is what the sa account is, a SQL Server account).

If you want to login from  your machine in a different domain, I believe that's the only way.
0
 
Harish VargheseProject LeaderCommented:
There are options to use different windows credentials to connect to SQL Server. You may add a saved windows credentials to connect to the sql server as below:

1. Go to Windows Credential manager
2. Add a windows credential (if there isn't already one pointing to your server)
3. At Internet or network address type:    <ServerName or address>:1433     (assuming you are using the default port, if you are using a different port then give that port number)
4. UserName = User account on the sql server domain
5. password = the password associated with the UserName

Now open Management Studio and try to connect to your sql server using windows authentication. Though it will still show your local username in greyed out field, it will use the saved credentials. There are chances of this option not working, but give it a try.

Source: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/c05a90e4-cb16-46f6-9072-37083c65696d/login-to-sql-using-windows-7-sql-server-error-18452-login-failed-the-login-is-from-an-untrusted?forum=sqlsecurity
-Harish
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 9
  • 5
  • 4
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now