Results 1 to 8 of 8

Thread: SQL Server Connection string?

  1. #1
    Sublime HEXUS.net
    Join Date
    Jul 2003
    Location
    The Void.. Floating
    Posts
    11,819
    Thanks
    213
    Thanked
    233 times in 160 posts
    • Stoo's system
      • Motherboard:
      • Mac Pro
      • CPU:
      • 2*Xeon 5450 @ 2.8GHz, 12MB Cache
      • Memory:
      • 32GB 1600MHz FBDIMM
      • Storage:
      • ~ 2.5TB + 4TB external array
      • Graphics card(s):
      • ATI Radeon HD 4870
      • Case:
      • Mac Pro
      • Operating System:
      • OS X 10.7
      • Monitor(s):
      • 24" Samsung 244T Black
      • Internet:
      • Zen Max Pro

    Question SQL Server Connection string?

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

    Code:
    	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:

    Code:
    	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?
    (\__/)
    (='.'=)
    (")_(")

  2. #2
    Sublime HEXUS.net
    Join Date
    Jul 2003
    Location
    The Void.. Floating
    Posts
    11,819
    Thanks
    213
    Thanked
    233 times in 160 posts
    • Stoo's system
      • Motherboard:
      • Mac Pro
      • CPU:
      • 2*Xeon 5450 @ 2.8GHz, 12MB Cache
      • Memory:
      • 32GB 1600MHz FBDIMM
      • Storage:
      • ~ 2.5TB + 4TB external array
      • Graphics card(s):
      • ATI Radeon HD 4870
      • Case:
      • Mac Pro
      • Operating System:
      • OS X 10.7
      • Monitor(s):
      • 24" Samsung 244T Black
      • Internet:
      • Zen Max Pro
    *Bump*
    (\__/)
    (='.'=)
    (")_(")

  3. #3
    Ex-MSFT Paul Adams's Avatar
    Join Date
    Jul 2003
    Location
    %systemroot%
    Posts
    1,926
    Thanks
    29
    Thanked
    77 times in 59 posts
    • Paul Adams's system
      • Motherboard:
      • Asus Maximus VIII
      • CPU:
      • Intel Core i7-6700K
      • Memory:
      • 16GB
      • Storage:
      • 2x250GB SSD / 500GB SSD / 2TB HDD
      • Graphics card(s):
      • nVidia GeForce GTX1080
      • Operating System:
      • Windows 10 x64 Pro
      • Monitor(s):
      • Philips 40" 4K
      • Internet:
      • 500Mbps fiber
    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.
    ~ I have CDO. It's like OCD except the letters are in alphabetical order, as they should be. ~
    PC: Win10 x64 | Asus Maximus VIII | Core i7-6700K | 16GB DDR3 | 2x250GB SSD | 500GB SSD | 2TB SATA-300 | GeForce GTX1080
    Camera: Canon 60D | Sigma 10-20/4.0-5.6 | Canon 100/2.8 | Tamron 18-270/3.5-6.3

  4. #4
    Sublime HEXUS.net
    Join Date
    Jul 2003
    Location
    The Void.. Floating
    Posts
    11,819
    Thanks
    213
    Thanked
    233 times in 160 posts
    • Stoo's system
      • Motherboard:
      • Mac Pro
      • CPU:
      • 2*Xeon 5450 @ 2.8GHz, 12MB Cache
      • Memory:
      • 32GB 1600MHz FBDIMM
      • Storage:
      • ~ 2.5TB + 4TB external array
      • Graphics card(s):
      • ATI Radeon HD 4870
      • Case:
      • Mac Pro
      • Operating System:
      • OS X 10.7
      • Monitor(s):
      • 24" Samsung 244T Black
      • Internet:
      • Zen Max Pro
    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..
    (\__/)
    (='.'=)
    (")_(")

  5. #5
    Senior Member GAteKeeper's Avatar
    Join Date
    Feb 2004
    Location
    Derbyshire, UK
    Posts
    582
    Thanks
    14
    Thanked
    28 times in 22 posts
    • GAteKeeper's system
      • Motherboard:
      • MSI P67-GD5
      • CPU:
      • Intel i7 2600k
      • Memory:
      • 8Gb Corsair DDR3 1600
      • Storage:
      • ~44TB
      • Graphics card(s):
      • 980Ti
      • PSU:
      • Seasonic S12 600W
      • Case:
      • Lian Li PC-65
      • Operating System:
      • Win10 64bit
      • Monitor(s):
      • Dell U3415W & 2405fpw
      • Internet:
      • 45Mb vDSL
    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
    Keeper of the Gates of Hell

  6. #6
    Sublime HEXUS.net
    Join Date
    Jul 2003
    Location
    The Void.. Floating
    Posts
    11,819
    Thanks
    213
    Thanked
    233 times in 160 posts
    • Stoo's system
      • Motherboard:
      • Mac Pro
      • CPU:
      • 2*Xeon 5450 @ 2.8GHz, 12MB Cache
      • Memory:
      • 32GB 1600MHz FBDIMM
      • Storage:
      • ~ 2.5TB + 4TB external array
      • Graphics card(s):
      • ATI Radeon HD 4870
      • Case:
      • Mac Pro
      • Operating System:
      • OS X 10.7
      • Monitor(s):
      • 24" Samsung 244T Black
      • Internet:
      • Zen Max Pro
    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..
    (\__/)
    (='.'=)
    (")_(")

  7. #7
    Sublime HEXUS.net
    Join Date
    Jul 2003
    Location
    The Void.. Floating
    Posts
    11,819
    Thanks
    213
    Thanked
    233 times in 160 posts
    • Stoo's system
      • Motherboard:
      • Mac Pro
      • CPU:
      • 2*Xeon 5450 @ 2.8GHz, 12MB Cache
      • Memory:
      • 32GB 1600MHz FBDIMM
      • Storage:
      • ~ 2.5TB + 4TB external array
      • Graphics card(s):
      • ATI Radeon HD 4870
      • Case:
      • Mac Pro
      • Operating System:
      • OS X 10.7
      • Monitor(s):
      • 24" Samsung 244T Black
      • Internet:
      • Zen Max Pro
    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...
    (\__/)
    (='.'=)
    (")_(")

  8. #8
    Ex-MSFT Paul Adams's Avatar
    Join Date
    Jul 2003
    Location
    %systemroot%
    Posts
    1,926
    Thanks
    29
    Thanked
    77 times in 59 posts
    • Paul Adams's system
      • Motherboard:
      • Asus Maximus VIII
      • CPU:
      • Intel Core i7-6700K
      • Memory:
      • 16GB
      • Storage:
      • 2x250GB SSD / 500GB SSD / 2TB HDD
      • Graphics card(s):
      • nVidia GeForce GTX1080
      • Operating System:
      • Windows 10 x64 Pro
      • Monitor(s):
      • Philips 40" 4K
      • Internet:
      • 500Mbps fiber
    If you want to try SQL authentication, here's how I set my database up:

    In SQL Enterprise Manager, drill down to:
    Code:
    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:
    Code:
    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:
    Code:
    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.
    ~ I have CDO. It's like OCD except the letters are in alphabetical order, as they should be. ~
    PC: Win10 x64 | Asus Maximus VIII | Core i7-6700K | 16GB DDR3 | 2x250GB SSD | 500GB SSD | 2TB SATA-300 | GeForce GTX1080
    Camera: Canon 60D | Sigma 10-20/4.0-5.6 | Canon 100/2.8 | Tamron 18-270/3.5-6.3

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 11
    Last Post: 08-03-2005, 10:15 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •