Solved

Not a valid User of a trusted SQL Server connection?

Posted on 2000-04-19
15
806 Views
Last Modified: 2011-09-20
'Login failed for User XDOMAIN_testuser - Not a valid User of a trusted SQL Server connection? (80004005)'

I get this message when trying to execute a stored procedure from a client-machine.

I am using VB with this connection Properties:

mCn.Provider = "sqloledb"

and

mCn.Properties("Integrated Security").Value = "SSPI"

I tried to insert the user as Login with the name XDOMAIN_testuser and with testuser (because the server is in the same domain)
I granted Execute permissions but without success.

What's wrong?

db
0
Comment
Question by:db_tiger
  • 6
  • 3
  • 3
  • +1
15 Comments
 
LVL 2

Expert Comment

by:p_biggelaar
ID: 2730587
I assume mCn is a ADO connection object. One of the things you could do is use a connectionstring like this:
       cn.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=<userId>;Initial Catalog= <Databasename>;Data Source=<ServerName>;pwd=<PassWord>"

This should do the trick. Besides you shouldn't need the code you already have. If you still experience trouble, use ODBC with a DSN for your SQL-server with all the necesary info in it.
0
 
LVL 1

Author Comment

by:db_tiger
ID: 2730734
I already tried to switch to ODBC: It worked, but produces problems with datatype conversion.
I am using now a the code fragment:

mCn.Properties("User ID").Value = "sa"
mCn.Properties("Password").Value = "..."

which is similar to your ihints and it works fine.

I would appreciate to understand the original problem: Integrated Security = "SSPI" means, that the WindowsNT autentication is used.
But I didn't find any posibbility to set my user as a trusted one or to add him i a SQLServer group or something like that.

db
0
 
LVL 2

Expert Comment

by:p_biggelaar
ID: 2731151
As far as I know there is no parameter that SQLOLEDB accepts close to "Integrated Security". See the entry in the MSDN library | Index | SQLOLEDB. When clicked you'll get a pop-up that will ask you to choose between two possiblities. Choose: Microsoft OLE DB Provider for SQL Server | Microsoft ActiveX Data Objects (ADO)

You should use Trusted_Connection to enforce this. If set to "yes" you will use NT authentication, if "no" you will use mixed mode. Please make sure that you do have a trusted connection (like named pipes)

SSPI is just an object that knows some security protocols, but is not necesarrily using the NT authentication scheme (NTLM)

Hope this helps...
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 2731987
p_biggelaar: integrated security is a parameter for SQLOLEDB, but you can use Trusted_Connection as well.
db_tiger:
You need to ensure that the TCP/IP protocol is enabled on both the client and the server. even if you main protocol may be Named Pipes, on TCP/IP connection will be made to check integrated security.
You may explain how you added the user to the SQL Server to grant him the Execute Permission, you should also tell us the version of SQL Server (to be able to guide through the forms if necessary)

Hope this helps

0
 
LVL 2

Expert Comment

by:p_biggelaar
ID: 2732670
angelIII: Thanks for the info on the parameter
0
 
LVL 7

Expert Comment

by:spiridonov
ID: 2733596
What SQL Server version are you using?
0
 
LVL 1

Author Comment

by:db_tiger
ID: 2733681
p_biggelaar:
Here is an extract from msdn on the Integrated Security parameter, that I already found yesterday, but due to technical reasons couldn't post - just to complete the things ('INFO: Initialization Properties for SQLOLEDB OLE DB Provider'):
'A string containing the name of the authentication service. The default value for this is VT_EMPTY. This can be set to "SSPI" or to a null BSTR pointer for Windows NT Integrated security (Secured Support Provider Interface). If SQL security is desired, the "Password" and "User ID" properties should be set instead.'s what I did - using password and UserID.

I see, that Trusted_Connection does the same thing - as angelIII posted.

I would like to figure out, how this thing would work with 'SSPI'.

angelIII + spridonov:
I am using SQL-Server 6.5

I did not use anything like NamedPipes, just used the ServerName and let OLEDB find the server throughout the INTRA-Net. The INTRA-Net is based on TCP/IP.

I registered the user as a New User - both with the short name 'testuser' and with the domain 'XDOMAIN_testuser' (as mentioned in the error message). I gave them 'Execute'-rights on the database - and I checked although the rights on the database, where I found the user.

In my opinion the problem is NOT the right in SQL-Server, but in the NT-UserManagement - the Error message says, the 'Login fails'. But I didn't find any user-group to set something like a 'trusted SQL-Server connection'.

db

0
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.

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 2733851
You are right when you say that named pipes is nothing you set in the ADO connection settings!
You need to check on the server the Server Network Utility (in the SQL Server Menu, not the NT Network Utility)
On the client PC you need to check the Client Netork Utility, there you find the protocols defined on the server/client that may be used for an SQL connection. You might explicitely configure your SQL Server here.

Is the SQL Server installed on a NT Server or NT Workstation. In the latter case, you need to enable some access rights for the NT user on this machine.

Hope this helps
0
 
LVL 1

Author Comment

by:db_tiger
ID: 2733926
SQLServer is a NTServer.
I did not find a Server Network Utility, but a SQL Server Client Configuration Utility. There is a Tab called Net Library and there is selected Named Pipes.

But I found something different: The SQL Security Manager and there I found, that no user is authorized, perhaps this could be the right loacation.
I did only try it in the SQL-Enterprise Manager.

I'm currently busy, will perhaps try it later or next week, bur would appreciate your opinion.

db
0
 
LVL 7

Accepted Solution

by:
spiridonov earned 100 total points
ID: 2733934
If you are using 6.5 and you want to use trusted connections You need to use SQL Security Manager to create logins in your database instead of using SQL Enterprise Manager.
0
 
LVL 1

Author Comment

by:db_tiger
ID: 2734283
I'll check this next week.

spiridonov, probably I won't accept your answer, even if it works, because I already got couple of usefull help from p_biggelaar and angelIII.
Should spilt teh points.
db
0
 
LVL 7

Expert Comment

by:spiridonov
ID: 2737029
>>I already got couple of usefull   help from p_biggelaar and angelIII.

Did it help your to solve THIS problem?
0
 
LVL 1

Author Comment

by:db_tiger
ID: 2755664
I added Users to the SQLSecurity and would like to reject the group, but it doesn't work. Once added, I am not able to remove the group Users (for purpose of testing).
May I have to wait long (10 minutes or more) because it's a really big domain?

I had some really interesting effects: If I map a Explorere-Connection to the SQL-Server using the Administrator-Share (d$) with Adminstrator-password, the login suceeds. If I remove this mapping, it fails.

It works now with a new group calledTestSQLUsers, where I added in the NT-UserManager the test_user, then added the group in SQLSecurityManager as spiridonov psoted and granted access to the database.
I am able to call a stored procedure, but I am revoked while trying to call a 'Select' statement - so I opened the database and tried to grant Select-permission - it's now pending since 5 minutes - how long may this pend?
And why? Because the domain is that big?

You are right in some way, spiridonov.

But you answered the question, when I already figured out the way to do it using the other infos.
The only 'help' you gave is to confirm, that it will work this way. Is that worth beeing THE answer?

db

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 2755859
Syncronistation in a domain is normally in time of 10 minutes, but for new items (users) this takes 2 steps (=20 minutes). In W2K, the syncronisation may even take longer, as there is no longer a PDC who sync all the others in 1 shot, but all the DC sync with 2 neighbours...

For your side effect when mapping a drive on the SQLServer, i come back to my comment: maybe the nt users do not have connect permissions to the SQLServer PC (speaking of NT connect permissions!).
You can verify this by cecking that all you users have the following permission: Access This Computer From Network (found in policies). This is normally enabled on NTServer for everybody by default, and normally disabled for everybody on NTWorkstation by default.
 
0
 
LVL 1

Author Comment

by:db_tiger
ID: 2755907
angelIII
I looekd it up, but it is as usual on NTServer: Everyone has access to this computer from the network.
The sideeefect is really strange, because before I recognized the difference I really wondered, why once it worked and then again didn't.

I managed now to grant the Select-permission to my XDOMAIN_testuser; it wasn't a matter of synchronistaion, but I did not handle the gui correct.

The thing works now both ways: Using 'UserId' and 'Password' or using 'Integrated Security'

I'll post these points for spiridonov and post to other question with 50 points for you and p_biggelaer. I appreciate your information, but I have to admit, that my original question was answered by spiridonov even though he did not much work.
 bye db
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

747 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

10 Experts available now in Live!

Get 1:1 Help Now