SQL Server Connection string?

[Stoo]

Okay, this is doing my nut in today..

I'm doing a little test script to test a new sql server we've got installed here, fairly basic stuff, but for some reason it absolutely refuses an ASP connection..

	strCon = "Provider=sqloledb;" & _
           "Network Library=DBMSSOCN;" & _
           "Data Source=xxx.xxx.xxx.xxx,1433;" & _
           "Initial Catalog=mydatabase;" & _
           "User ID=blah;" & _
           "Password=blah"

That's my connection string, which fails with:

Microsoft OLE DB Provider for SQL Server (0x80004005)
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

If I use this connection string:

	strCon =  "Provider=sqloledb;" & _ 
           "Data Source=MyDBServer;" & _
           "Initial Catalog=Mydatabase;" & _
           "User Id=blah;" & _
           "Password=blah"

I get the error:

Microsoft OLE DB Provider for SQL Server (0x80004005)
Login failed for user 'blah'. Reason: Not associated with a trusted SQL Server connection.

Now I've read through things and it suggests that the problem lies with having a windows only authentication setting, and to change it to SQL Server & Windows instead, but the system is already configured to SQL Server & Windows authentication..

Yet I can easily connect to the database using aespe Table Browser, which uses virtually the same connection method..

Any ideas?

[Stoo]

*Bump*

[Paul Adams]

Hmm, I always have an interesting time when I rebuild my web server & SQL setup, and they run on the same box!

My ASP code is as follows:

dsn = "DSN={DSN defined for site};uid={user with rights to db};pwd={password for user account}"
set db = server.createobject("adodb.connection")
db.open dsn

This is with SQL authentication, not Windows, and the default db is set on the DSN so I don't need to specify it in the connection string.
The thing that always trips me up is that I forget to tell MSDE to allow network connections via TCP, so the server is not contactable over the network - no idea if SQL is the same as I've not really played DB admin much.

[Stoo]

Hmm, apparently it might be a problem with user rights over the domain (the dev server is outside the domain, the SQL Server is inside), as I've tried both windows and sql users, even used the domain admin login through sheer fustration, still no go..

[GAteKeeper]

Off the top of my head SQLserver can be configured in 3 ways; Domain Auth, SQL users auth or a combo of both.

Sounds like that its set to domain auth only and the username hasnt got the right active directory privs. You could try a domain prefix on the username.

GAteKeeper

[Stoo]

iirc it's using the combo config, I think it might be something to do with the IUSR account of the testing box not being in AD, as I can use any username (sql, or windows) to the same effect..

[Stoo]

Hmm, well I added the IIS user accounts into AD after making the testing box a part of the domain, then imported the account into the SQL Server accounts section..

No Joy..

Pretty much at a loss now...

[Paul Adams]

If you want to try SQL authentication, here's how I set my database up:

In SQL Enterprise Manager, drill down to:

Microsoft SQL Servers
'-SQL Server Group
  '-<server name>\<instance name>
    '-Security
      '-Logins

Create a user object manually in here, select SQL Server Authentication and set the default database to the one you want.
On the Database Access put a tick in the box next to the database, then tick every right except for db_denydatareader & db_denydatawriter (I fell into that trap once, it overrides all other privileges and blocks the user account, d'oh!).

Then drill down to the following location:

Microsoft SQL Servers
'-SQL Server Group
  '-<server name>\<instance name>
    '-Databases
      '-<database name>
        '-Users

Create a user object here with the same name and permissions as above.

Now go into Control Panel / Administrative Tools / Data Sources (ODBC) and create a new system DSN using the following options:
1.
SQL Server (right at the bottom of the list)

2.
Name: (helpful name for calling from ASP)
Description: (something for you to remember what this is for)
Server: (drop-down box hopefully locates your SQL Server to select)

3.
SQL Server Authentcation
(leave box ticked for connecting to the server to obtain default settings)
Check in Client Configuration that TCP/IP is the protocol of choice
Enter the username and password you set up in Enterprise Manager earlier

Now with a bit of luck when you complete this DSN setup it will pass the test, to indicate your SQL configuration is at least okay.


Now to use the DSN from ASP:

dsn = "DSN={helpful name from step 2 above};uid={DB useryou set up};pwd={password for user}"
set db = server.createobject("adodb.connection")
db.open dsn

And when you're done, db.close and set db=Nothing

Can't promise anything, but it works for me - I originally used SQL Server 2000 and now MSDE and I run 3 databases accessible from sites running on the same server this way.