i installed on SQL studio manager and attach database but i am not able to connect through SQL authentication only through Windows autantication

actualy i moved SQL databace from old machine to this machine
in the old machine i was able to loging through SQL autentication with user sa and password
here i would like also to login with same credentail how do i do that?

do i need that SQL authentication will work if i am login through external ip like in the old machine?

Thanks
Gvilbis
gvilbisAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ShareD_PointCommented:
Hi,

The Server authentication mode of the new SQL server may be set to "windows Authentication Mode".

1) You need to change the server authentication mode to “SQL server and Windows Authentication mode”. (Go to  SQL Mgmnt Studio  -> SQL Server Properties -> Security Tab-> Server Authentication )
Restart SQL Server

2) Enable SA account and set the password.

Hope this helps
gvilbisAuthor Commented:
do i have to set user sa in the proxy account? see print screen
or else? because i tried to connect after i set there and i received error when connection with SQL autentication the error saying that success to establish connection but there is error with user
Screenshot-2013-10-21-15.29.50.png
Scott PletcherSenior DBACommented:
The native SQL (non-Windows) logins have a different sid on the new machine, so SQL can't automatically link the database user and the SQL login -- the problem is commonly called "orphaned user".

You need to re-link each SQL Server login with its appropriate login.  Run the command below for every native SQL Server login to re-sync it:

USE [db_name_with_affected_users]
EXEC sp_change_users_login 'UPDATE_ONE', '<user_name>', '<login_name>'
EXEC sp_change_users_login 'UPDATE_ONE', '<user_name2>', '<login_name2>'
--...
--NOTE: Don't use the 'AUTO_FIX' option, since it can be flaky.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

gvilbisAuthor Commented:
so the line that i will have to run in the query should be:

USE [wf10]
EXEC sp_change_users_login 'UPDATE_ONE', 'sa', 'sa'

is that correct? i do not need to set password? the password is as it define in the machine?
Scott PletcherSenior DBACommented:
Sorry, I misunderstood the problem.

You don't need to reset 'sa', since it has a fixed sid.  I thought you were having issues with other native SQL logins.
ShareD_PointCommented:
Enable TCP/IP or Name Pipes under Protocols for SQL Server (From SQL Server Configuration manager) and try.

Hope this may helps
gvilbisAuthor Commented:
this is what i have in tcp/ip see print screen

what to set there, how do i config it?
gvilbisAuthor Commented:
i forgot to attach the picture
Screenshot-2013-10-21-16.22.42.png
ShareD_PointCommented:
Check under the SQL Server Network Configuration.
Select the protocol and Set Enabled = 'Yes' in the properties window.  

(A different thought: doubt this may not be the issue, as you already got an error message "success to establish connection but there is error with user ")
ShareD_PointCommented:
Pl share the exact error you received earlier.
gvilbisAuthor Commented:
here is print screen of the error
and how protocol is set in SQL server manager

please advice

Thanks
Screenshot-2013-10-22-09.59.41.png
Screenshot-2013-10-22-09.57.52.png
ShareD_PointCommented:
This is a case of login failed for user (Error No : 18456).
Get the detailed error log from SQL Server instance error log. You may find some thing like this.
" 18456 Severity : 14 State : 1 Line Number : 123".

We can figure out the issue based on the Error  state code.

ERROR STATE       ERROR DESCRIPTION
--------------------------------------------------------------------
2 and 5                    Invalid userid
6                               Attempt to use a Windows login name with SQL Authentication
7                               Login disabled and password mismatch
8                               Password mismatch
9                               Invalid password
11 and 12               Login but server access failure
13                            SQL Server service paused
18                            Change password required

Ref URL:
http://www.katieandemil.com/microsoft-sql-server-error-18456-login-failed-for-user

Hope this helps.
gvilbisAuthor Commented:
where i can find - "Get the detailed error log from SQL Server instance error log" the log file?

one more things i see that SQL Agent process is not coming up, is it mean that something is wrong? because in the old machine it was running

second in the Old machine the SQL user was "sa" and pass "15848" and now i am on new machine Windows server 2012 and in order to pass validation password i change the password to new one however i tried to log in with both password unsuccessfully

please advice

Thanks
Gvilbis
gvilbisAuthor Commented:
here is print screen of the error in log file
how i can correct it

Thanks
Screenshot-2013-10-22-12.42.51.png
ShareD_PointCommented:
Check the following.

Connect the SQL Server (Using windows authentication).
Under Your SQL Server Instance  ->Security - > Logins, Select the SA account
Go to the Properties of SA, under the Status Tab make sure the following
Under settings ->
Permissions to connect a database engine: should be Grant
Login: should be Enabled

Under Status ->
SQL Server authentication: Login is lock out: should be unchecked or Grayed out

In one of your screen capture it is found that sa account is used as a proxy account. That's not correct.  (You asked this question earlier. Sorry about it)
Use a windows account to connect proxy if it is required. Otherwise disable that option by uncheck the Enable server proxy account.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gvilbisAuthor Commented:
when you saying connect to SQL Server you mean Studio Manager?
and where its SQL Server instance?

thanks
gvilbisAuthor Commented:
OK i found it but to set SQL is grayout see print screen:

attach
Screenshot-2013-10-22-14.53.19.png
Screenshot-2013-10-22-14.53.24.png
gvilbisAuthor Commented:
ok founded

Thanks for the help
ShareD_PointCommented:
Good to know you got the answer.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.