Solved

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

Posted on 2009-05-09
14
719 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
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…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …

696 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