Solved

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

Posted on 2009-05-09
14
710 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
  • 7
  • 6
14 Comments
 
LVL 142

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
 
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
2008 to 2016 SQL migration.. 5 29
Oracle - How to analyze data using DATE COLUMN? 7 60
SQL Query Syntax Error 9 33
sql query Help 12 17
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

706 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

19 Experts available now in Live!

Get 1:1 Help Now