Solved

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

Posted on 2014-02-23
23
221 Views
Last Modified: 2014-03-03
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
Comment
Question by:Ihab
  • 9
  • 5
  • 4
  • +3
23 Comments
 
LVL 12

Expert Comment

by:Harish Varghese
Comment Utility
Yes. What error do you get when you try windows authentication?
0
 
LVL 22

Expert Comment

by:Steve Wales
Comment Utility
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
 

Author Comment

by:Ihab
Comment Utility
Check the message
SQl1.jpg
0
 
LVL 16

Expert Comment

by:Kamal Khaleefa
Comment Utility
yes you can connect using windows authentication and sql authentication

see this
http://technet.microsoft.com/en-us/library/ms345332.aspx
0
 

Author Comment

by:Ihab
Comment Utility
When I choose windows authentication. The fields are dimmed, not allowing to enter username and password
0
 
LVL 12

Expert Comment

by:Harish Varghese
Comment Utility
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
 

Author Comment

by:Ihab
Comment Utility
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
 
LVL 12

Accepted Solution

by:
Harish Varghese earned 200 total points
Comment Utility
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
 

Author Comment

by:Ihab
Comment Utility
After I enable this , I will be able to connect.
0
 
LVL 12

Expert Comment

by:Harish Varghese
Comment Utility
Do you mean that you enabled this and you able to connect now?
Or have you not tried this yet?
0
 
LVL 5

Expert Comment

by:chanderpal singh rathore
Comment Utility
Hi  ihab_h,

Yes, you can login from other machines just after enabling remote login
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:Ihab
Comment Utility
I will try it and I will come back to you
0
 
LVL 22

Expert Comment

by:Steve Wales
Comment Utility
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
 
LVL 24

Expert Comment

by:DBAduck - Ben Miller
Comment Utility
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
 

Author Comment

by:Ihab
Comment Utility
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
 

Author Comment

by:Ihab
Comment Utility
Can I add user other than sa in the sql?
0
 
LVL 24

Expert Comment

by:DBAduck - Ben Miller
Comment Utility
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
 

Author Comment

by:Ihab
Comment Utility
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
 
LVL 22

Assisted Solution

by:Steve Wales
Steve Wales earned 200 total points
Comment Utility
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
 

Author Comment

by:Ihab
Comment Utility
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
 
LVL 24

Assisted Solution

by:DBAduck - Ben Miller
DBAduck - Ben Miller earned 100 total points
Comment Utility
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
 
LVL 22

Assisted Solution

by:Steve Wales
Steve Wales earned 200 total points
Comment Utility
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
 
LVL 12

Assisted Solution

by:Harish Varghese
Harish Varghese earned 200 total points
Comment Utility
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

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now