Solved

How to connect to SQL Server via script (ASP) from website (All locally) with Windows Authetication

Posted on 2009-05-09
14
717 Views
Last Modified: 2012-05-06
I have installed Microsoft SQL SQL Server 2008 Express. I wanted to install it setting up SQL Server Authentication so I could use userid=sa and password=myPass BUT I couldn't work it out so it's set up with Windows Authentication.

Using the SQL Server Management software I can open all my databases under my server (MYPC\SQLEXPRESS). Now I need to know how from ASP I can connect to my databases.

I've set out the bits I know of my connection string below, and then below that is how it used to be:
Set conn = Server.CreateObject("ADODB.Connection")
    conn.Open( ...what goes here now...? )
    conn.Close
Set conn = Nothing
 
How I used to connect:
Set conn = Server.CreateObject("ADODB.Connection")
    conn.Open( driver={SQL Server}; server=MYPC\SQLEXPRESS; database=DBname; User ID=sa; password='myPass' )
    conn.Close
Set conn = Nothing

Open in new window

0
Comment
Question by:tobzzz
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
14 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24343338
connection strings here:
http://www.connectionstrings.com/sql-server-2005

note: from asp will be a bit more complicated, as for web-based applications, you need to configure the IIS web site to remove anonymeous access. this will NOT work for internet applications, except if the iis web service is configured to run under domain account, which in turn needs to be configured as windows login in the sql server security.
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24343352
0
 
LVL 11

Author Comment

by:tobzzz
ID: 24344550
Thanks to both of you, I had already visited the www,connectionstrings.com site. The first thing I noticed is that when using their strings I see this error:
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
The way of resolving this seems to be by adding "driver={SQL Server};" or "Provider=SQLOLEDB;".

When I use "Provider=SQLOLEDB;", the browser error is:
Invalid authorization specification
So I figure it's better to use "driver={SQL Server};"

Presumably I do not need to include a used id or pass in my string, because MYPC\SQLEXPRESS is set up as Windows Authentication. Is this correct? It seems to me I should use this line:
driver={SQL Server}; Server=MYPC\SQLEXPRESS; Database=dbName; Trusted_Connection=True;

The error I receive from using this is:
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database "dbName" requested by the login. The login failed.

@angelIII: When I was using MS SQL Express 2005 previously, I didn't have to change any IIS settings, I just used the string I specified in my first post. Do I have to fiddle with IIS settings because it's 2008? Ideally I would like to connect as previously on SQL Server Authentication using user id=sa but I simply don't know how to configure 2008 to do that. Any help would be greatly appreciated!
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 31

Expert Comment

by:RiteshShah
ID: 24344830
>>Cannot open database "dbName" requested by the login. The login failed.<<

are you sure, login you are using has proper rights to connect to database? are you able to do so from SSMS? because error is saying that you were about to connect to database but credential was rejected.
0
 
LVL 11

Author Comment

by:tobzzz
ID: 24344881
Well, when I open SSMS the first thing it does is ask me to connect to MYPC\SQLEXPRESS using Windows Authentication. I click OK and it connects and I can play around with my databases. I've right-clicked on MYPC\SQLEXPRESS > Properties > Security and can confirm the database is set to Windows Authentication only. So, if there is a used id and password, I don't know what it should be, do you think there should be? If so, how would I find out what it is?
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24344890
well if your SQL Server is windows Authenticated, you will not be able to connect via sql id/pass for sure, as long as id/pass concern, nobody can tell you, you must have given password for SA account while installing server. however, you can create new login/pass and select option for mixed authentication and check whether your new id/pass works or not.
0
 
LVL 11

Author Comment

by:tobzzz
ID: 24344978
Sounds good, can you tell me how I create new login and pass please? Lets say I set id as "sa1" and pass as "test", what will my connection string be? Like this again maybe:
Set conn = Server.CreateObject("ADODB.Connection")
    conn.Open( driver={SQL Server}; server=MYPC\SQLEXPRESS; database=dbName; User ID=sa1; password='test' )
    conn.Close
Set conn = Nothing

Open in new window

0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24344994
this could be your connection string.

"Provider = SQLNCLI;Data Source = MYPC\SQLEXPRESS;Initial Catalog = dbName;User ID = sa1;Password = 'test';"
0
 
LVL 31

Accepted Solution

by:
RiteshShah earned 250 total points
ID: 24344998
0
 
LVL 11

Author Comment

by:tobzzz
ID: 24345220
@RiteshShah: Thanks. I changed to mixed mode; both SQL Server Auth and Windows Auth. Then followed both links in order, all good. Then added your string but get a new error:

Microsoft SQL Native Client error '80004005'
Login failed for user 'sa1'. Reason: The password of the account must be changed.

followed both links again and made a new user called ts and changed my string, same error:

Microsoft SQL Native Client error '80004005'
Login failed for user 'ts'. Reason: The password of the account must be changed.

Is there something else I need to do? Feel like I'm getting much closer to solving this with your help so thanks again.
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24346859
:) well you must have selected option for change password at next logon...... anyway either change password for your existing user or create new login and user but don't forget to unselect two checkbox which marked in attached image while creating new login.


 
error.jpg
0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24346861
or you can run following script for your existing loing


ALTER LOGIN sa1 WITH CHECK_EXPIRATION = OFF

ALTER LOGIN sa1 WITH CHECK_POLICY = OFF



ALTER LOGIN ts WITH CHECK_EXPIRATION = OFF

ALTER LOGIN ts WITH CHECK_POLICY = OFF
0
 
LVL 11

Author Comment

by:tobzzz
ID: 24402038
@RiteshShah: Apologies for such a late reply, I had to go away for some days. I have just returned and read your last two posts. The latter fails for me saying it cannot alter login when policy and expiration are set to ON. However, the former post worked just great, I completely didn't notice those tick boxes. Thank you! The only other thing I needed to do to make it work was when adding the user to a specific database, I must select "dbowner" under "database role membership".

Thank you very much for persevering and helping me resolve this.
0
 
LVL 11

Author Closing Comment

by:tobzzz
ID: 31579753
The answers are spread across all the posts by RiteshShah, it would be worth reading all of them in order to find the solution if you are having the same problem.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Database Restore 2008 R2 1 41
MS SQL Conditional WHERE clause 3 47
SQL- GROUP BY 4 51
Tracking Problematic Page Splits 1 49
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

732 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