Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2014-02-23
23
Medium Priority
?
245 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 5
  • 4
  • +3
23 Comments
 
LVL 12

Expert Comment

by:Harish Varghese
ID: 39881750
Yes. What error do you get when you try windows authentication?
0
 
LVL 23

Expert Comment

by:Steve Wales
ID: 39881752
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
ID: 39881771
Check the message
SQl1.jpg
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 16

Expert Comment

by:Kamal Khaleefa
ID: 39881772
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
ID: 39881783
When I choose windows authentication. The fields are dimmed, not allowing to enter username and password
0
 
LVL 12

Expert Comment

by:Harish Varghese
ID: 39881784
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
ID: 39881797
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 600 total points
ID: 39881833
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
ID: 39881852
After I enable this , I will be able to connect.
0
 
LVL 12

Expert Comment

by:Harish Varghese
ID: 39881859
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
ID: 39881875
Hi  ihab_h,

Yes, you can login from other machines just after enabling remote login
0
 

Author Comment

by:Ihab
ID: 39881912
I will try it and I will come back to you
0
 
LVL 23

Expert Comment

by:Steve Wales
ID: 39882978
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 25

Expert Comment

by:DBAduck - Ben Miller
ID: 39887458
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
ID: 39889798
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
ID: 39889806
Can I add user other than sa in the sql?
0
 
LVL 25

Expert Comment

by:DBAduck - Ben Miller
ID: 39889820
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
ID: 39889866
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 23

Assisted Solution

by:Steve Wales
Steve Wales earned 600 total points
ID: 39889869
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
ID: 39889884
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 25

Assisted Solution

by:DBAduck - Ben Miller
DBAduck - Ben Miller earned 300 total points
ID: 39889899
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 23

Assisted Solution

by:Steve Wales
Steve Wales earned 600 total points
ID: 39889904
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 600 total points
ID: 39894211
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

Enroll in September's Course of the Month

This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are some very powerful Dynamic Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a di…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

722 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