Not a valid User of a trusted SQL Server connection?

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.

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

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

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

angelIII: Thanks for the info on the parameter

What SQL Server version are you using?

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

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

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

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

>>I already got couple of usefull   help from p_biggelaar and angelIII.

Did it help your to solve THIS problem?

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

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.
 

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