microsoft data link error test connection failed because of an error in initializing provider login failed

hey guys, I'm having an issue with connecting to a database using a .UDL file from my workstation, I can connect using the NT integrated security, but not with the sql account I created, I can telnet to port 1434 that I have it set up on with my logged in credentials, so I know it's there, I've changed the password, and am sure I'm using the same credentials to test with, but cannot get the dang thing to connect. I verified the sql browser service is running. any other ideas? I'm in no way much of a sql or DB guy, so any help pointing me in the right direction would be appreciated, it's on a 2012r2 server, and is SQL server 2012 developer. Thanks in advance.
Don KlahnSystem AdminAsked:
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.

Barry CunneyCommented:
Hi dklahn3,
Please post up the error that is being returned.

If possible, on the actual server where the SQL Server instance is running, simply try and connect and log in to the instance using SQL Server Management Studio.

Double-check the credentials that you are using.
If possible as a test try and log in with the 'sa' profile and then create a new SQL Server login and try and connect and log in with this new login.

Another thing worth checking is the Server Properties | Server authentication
In SQL Server Management Studio you can right-click on the instance and choose Properties
In the Server Properties dialog there is a Security page and in the top section there is 'Server authentication' which has 2 possible options: 1. Windows Authentication mode 2. SQL Server and Windows Authentication mode

Please let me know if any of this helps and please come back with more questions as necessary
Don KlahnSystem AdminAuthor Commented:
this is the error showing up in the logs, will be trying the other things you suggested shortly, thanks you!

event 18456 login failed for user XXXXX Reason: Login-based server access validation failed with an infrastructure error
Don KlahnSystem AdminAuthor Commented:
although, if I give this account SA rights, it does test successfully
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!

ZberteocCommented:
Are you sure you don'e have a deny access of some sort on that login?

Can you run this and see what will result:
select * from sys.server_permissions

select * from sys.server_principals

Open in new window

Barry CunneyCommented:
Hi dklahn3,
Yes, as Zberteoc advised, please check the permissions for the 'login' which is giving issue.
Particularly, check the 'Connect SQL' permission.

The permissions can also be checked via the GUI in SQL Server Management Studio.
Right-click on the 'login'  and choose Properties
In the 'Login properties' dialog, select the 'Securables' page
On the right hand part of the dialog in the bottom section, there are 'Explicit' and 'Effective' permissions.
Here you can scroll down through permissions and find the 'Connect SQL' permission.
Also while in this dialog check if the 'Deny' checkbox has been checked on for any permission.
Don KlahnSystem AdminAuthor Commented:
I am able to login using this account on the sql server, effective permissions show:
CONNECT SQL
VIEW ANY DATABASE
VIEW ANY DEFINITION

under explicit there aren't any deny boxes checked, and gran is checked under both connect sql, & view any definition
ZberteocCommented:
Remove that login and then add it back and make sure you map it on the database you need access to.
Barry CunneyCommented:
Hi dklahn3,
Just to fully clarify the scenario, please confirm the following:
1. When you are actually on the server on which the SQL Server instance resides you can successfully connect with the SQL login through SQL Server Management Studio?

2. When you specify the exact same SQL login in a .udl file on your workstation, you cannot connect?
Don KlahnSystem AdminAuthor Commented:
that is correct
Don KlahnSystem AdminAuthor Commented:
I tried removing the login recreated it, and still the same issue
Barry CunneyCommented:
Hi dklahn,
If possible, please open the .udl file using Notepad and post up the content.
Don KlahnSystem AdminAuthor Commented:
[oledb]
; Everything after this line is an OLE DB initstring
Provider=SQLOLEDB.1;Persist Security Info=False;User ID=XXX;Data Source=XXX
Barry CunneyCommented:
Hi dklahn3,
Please confirm if by any chance you have SQL Server Management Studio installed on your workstation.
Don KlahnSystem AdminAuthor Commented:
no I don't, that's why I'm using the .udl
Barry CunneyCommented:
Hi dklahn3,
On the server, please run a SQL Profiler trace.
Then try the .udl connection and see if there is any activity in the SQL Profiler trace
Please let me know if you need details on SQL Profiler - this is trace tool which can be accessed from the Tools menu in Management Studio
ZberteocCommented:
If he doesn't have the Management Studio you can forget about SQL Profiler... :)
Don KlahnSystem AdminAuthor Commented:
ok, I ran it, was going to upload it, but it's not recognizing the file format of .trc
Don KlahnSystem AdminAuthor Commented:
I tried searching through it using the find for that user account, and nothing is showing up, not sure what to look for
Don KlahnSystem AdminAuthor Commented:
I found this in another post, I do have a configMGREndpoint listed under service broker, but don't see anyway to grant permissions to it, see snipit attached.

The public server role had an endpoint called ConfigMgrEndpoint that had no permission to "Connect". I granted the connect privilege and things started to work for non-sa users.

Under Server Objects>Service Broker I find the ConfigMgrEndpoint.
Capture.PNG

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
ZberteocCommented:
ok, I ran it, was going to upload it, but it's not recognizing the file format of .trc
What exactly did you run and how?
Don KlahnSystem AdminAuthor Commented:
the sql profile trace, from the sql server
ZberteocCommented:
How? You said you didn't have Management Studio installed. And what .trc file you tried to use? I don't understand how did you get that error.
Don KlahnSystem AdminAuthor Commented:
I was asked if I had it on my workstation, which I don't, I ran it from the server, the output file is a .trc, which this site doesn't recognize as an uploadable file format
ZberteocCommented:
Just change the extension to .txt and then upload it
Don KlahnSystem AdminAuthor Commented:
ok, here it is
Untitled---1.txt
Don KlahnSystem AdminAuthor Commented:
thanks for all your help guys, I did get it figured out! It was the endpoint I mentioned earlier, finally figured out how to give it connect permissions, and everything is working as it should!
Don KlahnSystem AdminAuthor Commented:
because it was the answer I needed
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.