Hi everyone.
Wanted to let you know that we've just released Drupal 7 for Windows+IIS+MSSQL out on http://microsoft.com/web/drupal This distro, built by Commerce Guys comes pre-configured with the Drupal SQL Server Module. When you install this, our installer will check your system and install MS SQL Express and PHP if you need it automatically.
If you don't have IIS on your Windows machine clicking install will install Drupal 7 with MSSQL using a new web dev tool we released last week called WebMatrix. (htttp://microsoft.com/web/webmatrix). If you have IIS on your dev machine you will have a choice to install D7 to IIS or to WebMatrix. (I recommend trying WebMatrix). WebMatrix integrates our Web App Gallery with a lightweight dev environment and our hosting gallery where we have deals on hosting on Windows and IIS.
WebMatrix works on XPsp3 and up so should be something everyone can try.
Hope you guys like this. Let me know if you have any feedback. We're scoping out v2 for WebMatrix right now.
Thanks,
Comments
Cool! Last week a customer
Cool!
Last week a customer asked us to install a D6 site on their Windows machine as they are not familiar with Unix, Apache, PHP, MySQL (forgot something??) at all. On the other hand, we do not have any experience with IIS and use XAMPP or ZendServer for development.
Would you suggest using WebMatrix or IIS in such an environment?
I realize that this is most likely the wrong thread, but I joined this group only last week and the message from Mark is at exactly the right moment...
Thanx,
Marcel
WebMatrix is optimized for hosted environments
Few questions:
Is this for an intranet solution behind a firewall?
What version of Windows Server are they using?
Does the customer require SQL Server as well?
WebMatrix is not really geared for deploying sites behind a firewall on a users intranet. It's much more geared towards publishing to a hosted server.
Two ways you could do this.
Try WebMatrix and deploy to their intranet server. I've seen issues with this because the server needs to be setup and configured properly, The customer needs to be running Windows Server 2008 and should have the R2 update. The server will need to be prepped to run PHP and should have WebDeploy installed for IIS on that server so you can publish to it. Here is an article on WebDeploy http://iis.net/webdeploy
The second option is to remote into their server and install our Web Platform Installer on their server and then install Drupal 7 from there onto their server. The Web PI will automatically detect any missing dependencies on that server and install them, including PHP, SQL Server, etc. You can then configure the site as you normally would by either remoting into it or from the Admin pages themselves.
Btw, since you are new to PHP on IIS we created a series of articles that walks new users to IIS on how to setup/configure them to run PHP. Definitely the place to start. http://learn.iis.net/page.aspx/24/running-php-applications-on-iis/
Also, if you or anyone is interested in checking out WebMatrix and see the entire experience one of the hosting partners has got a three months free offer for new WebMatrix users so you can check out the whole experience from new site to publishing live, see what you think. http://www2.appliedi.net/webmatrixsignup/
hope some or all of this is helpful.
Hey Mark, Very helpful
Hey Mark,
Very helpful indeed! Will check out the articles and see what's the best way to go!
It's indeed an intranet, behind a firewall. Other details are still unknown to us, but if we go for IIS, it should be 7.5 I guess. Obviously we need the rewriting module and as far as I know, that's part of IIS nowadays. I used to work with the 3rd-party tool isapi_rewrite a couple of years ago, but wasn't completely happy by the way it handled .htaccess files (it onnly read one .htaccess in the root, but couldn't handle access files in subfolders)
Regards,
Marcel
Happy to help
Yes ISAPI rewrite was not an optimal solution.
URL Rewrite module in IIS is able to import rules out of htaccess which can be helpful if you have custom rules. Also, web.config while is our equivalent to htaccess is now included in Drupal 7 by default as you probably know as well.
If you want a deep dive on what our URL Rewrite module can do you can learn more here, http://iis.net/urlrewrite.
Hi Mark, I can see the Drupal
Hi Mark,
I can see the Drupal 7 with MS SQL is located here: http://www.microsoft.com/web/drupal.
However when I run the Microsoft Web Platform Installer from Start menu, I cannot find the Drupal installation from the commerce guys listed there. Looks like this Drupal installation is not listed in the Products list within WebPI and is only available for install by starting it from the first link above.
Is this the case or I am getting something wrong?
Thank you,
Martin Kulov
WAMP Server
I like WAMP Server. It has Apache, MySQL, and PHP, plus some other goodies. It's quick to get up and running, but you're still using Apache and MySQL.
I like VS.Php as an add-in to Visual Studio. It comes with Zend as a debugger, as well as its own version of Apache and PHP. You'll still need MySQL installed.
Brian Travis
Author, Pro Drupal 7 for Windows Developers
PDO Error on Windows 7
Mark, I spun up a new VirtualPC of Windows 7 ultimate and loaded the WebMatrix installer (very nice, by the way). But when I access the site, http://localhost:15340, I get:
Error messagePDOException: SQLSTATE[42S02]: [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name 'semaphore'.: SELECT expire, value FROM {semaphore} WHERE name = :name; Array ( [:name] => variable_init ) in lock_may_be_available() (line 165 of C:\Users\DrupalUser\Documents\My Web Sites\Drupal 7 Developer release\includes\lock.inc).
Any ideas?
Brian Travis
Author, Pro Drupal 7 for Windows Developers
Seems to be the query
SELECT expire, value FROM {semaphore} WHERE name = :name;
the query syntax seems to be incorrect (the bold part). I didn't see this when I installed on my test box, can you narrow down the repro steps? For now, do file a bug on this module so that it doesn't fall thru the cracks.
Database is empty
I don't see anything wrong with that syntax. the db_select() function requires table names to be curly-braced. The problem is that the table doesn't exist.
My process:
1) Created a fresh VirtualPC image of Windows Ultimate 7 N
2) Downloaded the link, "Install Drupal 7" on http://microsoft.com/web/drupal
3) Installed the platform with the defaults
4) Installed Drupal when prompted and put in the database credentials.
5) It said everything was ready, and your site is http://localhost:15340.
6) Loaded that page, and got the error:
PDOException: SQLSTATE[42S02]: [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name 'semaphore'.: SELECT expire, value FROM {semaphore} WHERE name = :name; Array ( [:name] => variable_init ) in lock_may_be_available() (line 165 of C:\Documents and Settings\btravis\My Documents\My Web Sites\Drupal 7 Developer release3\includes\lock.inc).
The database is there, along with a user who has admin access to it, but there are no tables. This query is trying to hit a table that doesn't exist.
I did the same thing on a well-used XP box and got the same exact error.
Brian Travis
Author, Pro Drupal 7 for Windows Developers
Needs install :)
Sometimes the redirect to install.php doesn't happen automatically. We are investigating the issue and trying to come up with a workaround.
This PDO error is just Drupal's way of saying that the database is empty (it is roughly the same message on all the databases and all the platforms). Just visit install.php and install normally.
Damien Tournoud
That worked!
Running core test suite now. Thanks, Damien!
Brian Travis
Author, Pro Drupal 7 for Windows Developers
Many errors with this installation
I have installed D7-MSSQL on one of the recommended (by Microsoft) servers, and have run into many errors. For instance, when attempting to upload a .po file for the locale module, it simply ceases to function (possible time out, but no error code). When attempting to reset a password, it cannot email and gives this error,
I am comfortable with the traditional LAMP setup, however a client has specific requirements for this project. So... this is my first foray into IIS in several years. Any help would be appreciated.
P.S. I tried Web Matrix and was able to complete the installation of the Web Commerce Guys distribution after uninstalling all SQL Server programs on my local machine. After the installation completed, however, all I get is a 500.0 error when attempting to view it in a browser.
Which server?
Which version of windows?
Vista and 7 do not come with an smtp server.
Server 2003 had one.
Server 2008 does not.
Server 2008 R2 does.
If it's one of the servers, you have to enable the iis6 metabase and smtp server.
Let me know and I can walk you through it.
Phil
Server
Hi Phil,
It was 2008, but I have discontinued service with that host as their technical support team was very unresponsive. I will attempt this again shortly on a new host and post the results.
Thanks,
Kevin
I was using 2008 and now 2008 R2...
Now I use a dynamic cloud server from 1and1. There regular hosting is not the greatest (whos is) but they have been great on the dedicated side. They provide an enterprise Cisco firewall as well. Use that instead of the windows firewall and coordinate the firewall ports with the ipsec policies and you will have nearly bulletproof security.
I have seen people try to use Plesk, only to realize it's horrible piece of stuff I would not want to step on.
Remote desktop is really convenient. Remember that Windows server has all ports closed except for 80 and 443 (remote desktop port) via ipsec policies. This is the opposite of Linux as far as I know. It takes a little more time to set up but the security is good.
As for smtp, it only takes about 10 minute to set up on Server 2008 R2. However, if you don't want to set up or manage smtp on the Server, I have found the smtp module (http://drupal.org/project/smtp) to be very handy (especially from my desktop or laptop during development.
Hope this helps,
Phil
New host has separate SMTP server
Logic Point has had very good customer service, and although they are on 2008 (not r2) we were able to install the smtp module and configure it to work properly.
Thanks for the info, as it has been extremely helpful since there isn't a lot of Drupal 7 information for IIS on SQL Server yet.
SMTP module vs php smtp configuration
If you are running multiple sites on Drupal and always us the same SMTP server, you can configure php to use that server via php.ini. If you open it, you will see the smtp settings about a third of the way down. That way you don't have to use the SMTP module.
Speaking of multisites (were we?), it's real easy in IIS. Just point your host headers to the same Drupal installation, add site folders with the same host header name and your in. Just don't delete the default site folder! For SEO, point either with or without the www to the drupal root. Point the other to a seperate folder with a separate web.config. I have one folder dedicated to this for all sites. Then configure a 301 redirect to make sure the search engines only see one. I made a short 3 minute video of this on www.siteperspectives.blip.tv.
I am doing the same thing for an Insurance company I am building a couple of sites for.
Once you get to know it, windows hosting is really easy. I just upgraded my home server to php 5.3 and mysql 5.5 and it's blazingly fast!
I wish it were that simple
I am having a heck of a time trying to get multisite running on IIS with MS SQL Server. I created 5 sites in subdirectories once the default site was created. For one (of the 5 sites), I can access the install.php page, but after entering all of the information for the database, it loops back to the same page, never completing the install (though creating some (32) of the tables in the db).
For the other 4 sites, I get a 500 error when navigating to them in a browser.
This is a Drupal 7 installation on IIS7 with MS SQL Server 2008, using virtual directories to point to the root folder. The .htaccess file has uncommented "RewriteBase /." The web.config file in the root has been updated to add the following:
<location path="." inheritInChildApplications="false"><system.webServer>
.....
</system.webServer>
</location>
And separate web.config files have been added to each subdirectory.
Also, sites.php has been amended with the required entries for each subfolder:
$sites['example.com/site1'] = 'example.com.site1';
$sites['example.com/site2'] = 'example.com.site2';
etc...
Any help would be appreciated.
Figured it out
I needed to Enable Basic Authentication and Enable Write Permissions for the virtual directories created. Once those settings were updated, everything worked great!
500 Error when uploading images
Hi Mark, we've had a bit of a rough ride putting Drupal 7 on Windows. Mainly because I've only ever used Apache before, not IIS.
Is this the best place to ask for D7/IIS advice? I have one question you may be able to help me with:
I've been having trouble uploading some images using the image field on a content type in the Drupal 7 admin pages. When I try to add an image I get a "AJAX HTTP request terminated abnormally" error popup. The Drupal report log show:
PDOException: SQLSTATE[23000]: [Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot insert duplicate key row in object 'dbo.file_managed' with unique index 'uri_unique'. in drupal_write_record() (line 6776 of C:\inetpub**************\includes\common.inc).
I have uploaded some images successfully to this and other content types but once this message appears I am unable to add any more images to that specific content type. I've tried renaming and selecting alternative images but this hasn't fixed the problem.
Our machine is: HP DL360, Windows Server 2008 64 bit, IIS 7, PHP v5.3.
And to install Drupal we used: IIS 7 Web Platform Installer and installed Drupal 7 Developer release (released on 19/01/2011).
Does anybody have any suggestions why this is happening?
And if you think I should be posting this elsewhere please let me know where's best.
Thanks!
@KingOfMyCastle
I am running the exact same set up as you, (iis7 PHP5.3 Windows Server 2008 64 bit) and have the exact same error. It began occurring after I set the image field to unlimited number of uploads. The uploading gif animation (rotating knob) does not stop. did you have any luck resolving it?
Cheers,
Njorl
No luck so far
I haven't fixed it but I think I know what's happening.
An image upload fails. This may be due to a normal upload error or possibly created due to the unique key created for each file based on the first few characters of the file name. The latter point is discussed here: http://drupal.org/node/1050800 There is a fix there too but it hasn't solved the problem.
IDs for the failed image upload are added to some tables and not others. These are the possible tables involved:
field_config_instance
field_data_body
field_data_field_banner_image
field_data_file
file_managed
file_usage
When you upload another file after a failure the IDs are out of sync and the database complains. So I think Drupal thinks "File number 5 being uploaded", "Oh, File 5 exists", "I'll put out this error message".
I have installed the latest dev version of http://drupal.org/project/sqlsrv which didn't fix it. Plus, I have just noticed a new dev version put up today so I'll try that.
But I'm unsure if the problem can be fixed by changes to sqlsrv or Drupal itself. Also I'm not sure if to fix this it will require a manual removal of rows from the tables listed above. Have you got any fresh ideas?
John..
Image uploading using Microsoft Sql with Drupal
I have the same problem. I haven't been able to upload any images at all.
PDOException: SQLSTATE[23000]: [Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot insert duplicate key row in object 'dbo.file_managed' with unique index 'uri_unique'. The duplicate key value is
in drupal_write_record() (line 6776 of \includes\common.inc).
Error (re)creating Content Type fields
Using a fresh install of Drupal 7 Preview on Server 2008R2 and the default SQLExpress from Web Platform Installer.
All goes well until adding fields to a Content Type, if the field name has been used before but deleted.
I consistently get an error when recreating a field after deleting it.
"There was a problem creating field Position Test: SQLSTATE[42S01]: [Microsoft][SQL Server Native Client 10.0][SQL Server]There is already an object named 'field_data_field_position_test_pkey' in the database.."
It appears when deleting a field, the key is not deleted, so when you go to recreate the field, one gets the error shown.
Can anyone see if this works for them, or if it's a bug in the Drupal code somewhere?
Submit this to SQL Project
I would submit this to the issue queue here to have them take a look. sounds like a bug.
http://drupal.org/project/sqlsrv
Updates on D7 + MSSQL 2008 + IIS7 ?
I am ramping up for a project to migrate our current CMS to D7 + MSSQL 2008 + IIS7.
Since the announcement in Jan 2011 of D7 on Windows+IIS+MSSQL, and the last posting on this thread in March 2011, I'm curious to know where this install stands?
Is it safe to proceed with installing D7 + MSSQL 2008 + Windows 2008 R2?
Thanks,
Ian
Update
@delikid - I have successfully created 5 sites in a multisite installation using this setup. I am still having issues with certain contributed modules that work in a LAMP stack, but not with IIS, though. I would suggest that you test all your modules, making sure they install correctly before going full-on with the development.
Best of luck.
Drupal Installation issue
I am trying to install a D7 site on Windows machine with IIS and MSSQL as database.
I try to install by using the link microsoft.com/web/drupal.
It said everything was ready, and your site is http://localhost:15340. but I got error table variable already exists. Any help please
vijaya
Please Create a Different Thread for your Issue
Please Create a different thread for your issue and add the exact text of the message you saw.
Installation Guide
Never had any experience installing Drupal on IIS and using MSSQL database and running a windows based drupal system I Just need some helpful links .
Can any one suggest some good links or some free videos
vijaya
Drupal using MSSQL and IIS
Our company are considering using DRUPAL on our MS SQL platform, all our other applications use IIS and the MS SQL database so do not want to introduce an additional database structure.
I would appreciate some feedback from people who are using this product combination, namely:-
I have had the following comments made to me::-
"It is possible to run Drupal on MSSQL, however it is not recommended. Drupal is not generally tested on MSSQL and so stability cannot be guaranteed"
"plus a number of the contributed modules that we would have used do not support MSSQL"
I have read through the various discussions and threads, and can see there were / are issues but these seem to be being resolved.
In your experience is the Drupal / MS SQL combination robust enought for commercial web sites and business processes? And, as an organisation are you comfortable with it.
I appreciate there are hundreds of contributed modules available, and are theses easily ported from say MYSQL to MS SQL or is there significant redevelopment work required to convert these modules?
I apologise if this is the incorrect forum to raise this query, but any support from the community would be much apprecated.
Thanks
Which version of Drupal?
It depends on which version of Drupal you are talking about. Drupal 7 is database-independent, so you can use Oracle, MSSQL Server or anything else. It's certainly capable of running (and does run) high-volume sites.
The "not generally recommended" is for Drupal 6 (which had limited database compatibility).
chicagomom on Twitter and d.o.
Most of the incompatibilities
Most of the incompatibilities have been addressed in the MS SQL driver so it should be rare that you find an incompatibile module.
Problem: Drupal Authentication with Active Directory 2003
Dear Drupal member,
I have a problem when I configure Drupal7 to Authentication with Active Directory windows server 2003
and later than I log in to drupal by using the user in AD but I get the error messages:
" Another user already exists in the system with the same email address. You should contact the system administrator in order to solve this conflict.
Sorry, unrecognized username or password. Have you forgotten your password?"
I really need help from you all.Please help me!
Thank you in advance
les
Mr.Lors Les
IT Networking
email:les.lors@gmail.com
Tel:(+855)16214860
www.zamanuniversity.edu.kh
Error when installing Drupal for Windows (Acquia)
First, I'm a complete newbie to Drupal, so please bear with me. I want to install Drupal for Windows using IIS 7.5 and Microsoft SQL Server like what's discussed here, but I'm running into issues:
These are the steps I've taken:
- Clicked on the install "Acquia Drupal" for Windows at http://www.microsoft.com/web/drupal
- Once the beginning of the install job was finished, select MSSQL for the database (instead of MySQL)
- After install, got prompted for database information (new or existing database with credentials; I don't know what to enter here, so I tried both)
I tried both creating a new database, which resulted in this:
And then I tried selecting "use existing database", which resulted in the following error:
What is going wrong with this install? I thought that this was supposed to be a smooth process, and I thought everything that I would need to use Drupal would be included? Argh!!!
I don't see that SQL Express got installed, so I can do that manually. But what should I do after that? Thanks for your help.
Hi Jay Were you installing
Hi Jay
Were you installing this on a machine that already had some form of sql server on it already? The prompt for SQL Server or MySQL should come at the beginning of the installation not the end - 2 radio buttons should have shown up. And yes if you select SQL Server and have nothing already on the box it should install sql server express as it would install mysql if you don't already have it. Occassionally, the prompt box for sql server may pop up behind the main installation - have you checked all your open windows?
Yes, I do have a full version
Yes, I do have a full version of MS SQL Server 2005 installed on my machine. The prompt for the database type (MSSQL or MySQL) was the first one I saw after the install initially started. After my N.A. made the change to how long an install could take, after a minute or so, that dialog appeared. Also, I did check all of my open windows, and nothing else was there.
I'm installing SQL Server 2008 R2 Express 64-Bit as we speak. It's asking me if I want "Windows authentication mode" or "Mixed Mode". Do you know which one I should choose?
Here's a full description of
Here's a full description of the difference:
http://msdn.microsoft.com/en-us/library/ms144284.aspx
I personally pick Mixed Mode so I can create a separate strong pwd for SA for the installation. I think what you were previously running into was authentication problems with your existing SQL Server installation. Has it been a while since you've logged into it?
Thanks for the quick replies
Thanks for the quick replies granola.
I tried the Mixed Mode with a strong password, and then ran into this install error:
TITLE: SQL Server Setup failure.
SQL Server Setup has encountered the following error:
The required MSI package 'c:\4e9443111b2bc0b5fa52aa6aec0690\1033_ENU_LP\x64\setup\SqlWriter.msi' doesn't exist.
Error code 0x84B20001.
BUTTONS:
OK
I'm not sure what that means. Do you have any suggestions?
did you do a complete
did you do a complete uninstall of your previous sql server installation? the new installation may be having some conflicts with the previous ones you did.
I can't uninstall SQL Server
I can't uninstall SQL Server 2005 completely, because I use it quite extensively for my work.
Also, I did note that during the attempted install, it stated that I could run both versions side-by-side.
Do you know if there is a way for me to use my existing version of MS SQL Server 2005 instead of the Express Edition?
From this:
From this: http://drupal.org/project/sqlsrv
It looks like I need 2008 or later to work with Drupal 7. It's too bad that I cannot keep SQL Server 2005 side-by-side with SQL Server 2008 Express Edition. I'm only setting this up locally for now, but it appears to be a pain.
Edit: We have a new server coming along soon that will have IIS 7.5 and SQL Server 2008, so I'll just wait until that's ready. It's too bad, because I was hoping to check it out locally until that new server is ready. But I'll just have to be patient.
Thanks SO much for your help granola. Have a good one.
Drupal on SQL 2005
Hi,
did you also consider a manual installation of Drupal? This way you could install the database on your SQL 2005 instance.
In case I can help you with some step by step guidelines. You need to: Create a web app or web application in IIS, configure an application domain, create an empty database in your SQL 2005 instance and and a login.
Note: please consider that SQL Server 2005 is not supported for Drupal deployment :-)
Best,
Alessandro Pilotti
MVP ASP.Net / IIS
Ok, I successfully installed
Ok, I successfully installed SQL Server 2008 R2 Express Edition w/tools locally yesterday, and I created a database named Drupal. I also just created a new login for that database that is not 'sa'.
After lunch around 1pm, I plan on retrying my install of Drupal 7. There's a way to do that within Web Matrix on my machine (Acquia Drupal), but do you recommend another installation process for Drupal 7? Thanks for your help!
Ok, I just tried to install
Ok, I just tried to install Acquia Drupal through the Web Matrix without success. I tried several different things to no avail:
Use Existing Database
1) Created database manually in SQL Server 2008 R2 Express Edition named "Drupal" with a new login for the database and database admin
2) Selected SQL Server as database (over MySQL), selected "Use Existing Database" from dropdownlist, and entered the credentials already created.
3) Received the following error:
Acquia Drupal
This product did not install successfully: The database 'Drupal' could not be created. Login failed for user 'DRUPALLOGIN'.
NOTE: I made sure that I entered the password correctly with several attempts without success.
Create New Database:
1) Deleted the already existing database and logins
2) Selected SQL Server as database (over MySQL), selected "Create New Database" from dropdownlist, and entered new database and login information. (NOTE: I also tried doing this with the logins intact and the database gone, but received the same result)
3) Both tries resulted in this error:
Microsoft Web Application Gallery
Cannot connect to the database ''.
Login failed for user 'drupal_sa'.
OK
So what should I do now? These are the exact same errors that I was receiving before I downloaded SQL Server 2008 R2 Express Edition. I'd appreciate your help. Thanks.
SQL Server Access
Hi,
first lets check the database connection.
Start a command prompt and run the following command replacing the instance name that you provided during setup (or just "." in case you installed an unnamed instance). e.g.: SQLEXPRESS and provide the SQL Server login you created for your Drupal db.
sqlcmd -S .\YOURINSTANCE -U drupal_user
At this point it will ask you for your password. Once logged in you'll see a simple prompt:
1>
write:
USE yourdrupaldb
GO
Check for error messages.
Now let's do an empiric test to see if you have the proper permissions:
CREATE TABLE quicktest (id int)
GO
DROP TABLE quicktest
GO
to exit:
QUIT
If you get till here without error messages, your DB setup is ok. :-)
Next step, we'll discuss Drupal DB access during setup.
Thanks for the reply. This is
Thanks for the reply. This is where I'm at:
I started the command prompt and entered the sqlcmd code, and it asked me for a password. But when I enter the password, I get this error: Login failed for user 'MYLOGINNAME'.
I'm able to open MS SQL Server Management Studio without a problem, and I can see the "Drupal" database along with the login, so I'm not sure what step I'm missing. Thanks for your help.
SQL Create DB, login and user
Hi,
that shows that there's something wrong with the db permissions. Here's a very simple script to create a DB, a login and a user:
From the command line, access you SQL instance as instance administrator, in this case using integrated authentication:
SQLCMD .\YOURINSTANCE -E
And then go with the following script. Replace 'drupal' with the login/username you want to create and 'drupal_db' with the database you want to create.
Don't forget to change the password too :-)
CREATE LOGIN drupal WITH PASSWORD='Y0urPas$word'
GO
CREATE DATABASE drupal_db
GO
USE drupal_db
GO
CREATE USER drupal for login drupal
GO
exec sp_addrolemember 'db_owner', 'drupal'
GO
exec sp_defaultdb 'drupal', 'drupal_db'
GO
Afterwards repeat the test in the previous post with the new credentials.
Note: you can of course do the same steps with SQL Server Management Studio, but it will take way longer and it will be harder to explain on a forum post :-)
Good morning alexpilotti, I
Good morning alexpilotti,
I followed your steps, and retried the initial command line prompt. After entering sqlcmd -S .\MYINSTANCE -U drupal, it asks me for a password. When I enter the password, I receive this error:
Msg 18456, Level 14, State 1, Server MYSERVERNAME\SQLEXPRESS, Line 1. Login failed for user 'drupal'.
I re-opened Mgmt Studio to see if the database and login are there, and they are. I can even see the "drupal" login when I view the "drupal_db" database's properties under "Permissions". But that's all that happens at this point.
I'm more familiar with Mgmt Studio than I am the command line honestly. Do you know how I can do this within Mgmt Studio? Thanks.
SQL Server Access
Hi,
here are the steps to create a login in SSMS (SQL Server Management Studio).
Consider also that the SQL Server authentication and authorization model is a bit different from e.g. MySQL:
A "login" is the principal that connects to the instance. You can have more than one SQL Server instance on your server (e.g .\SQLEXPRESS, .\MSSQL08, etc), each one with its logins.
A "user" is the principal that connects to a database. A "user" is tipically mapped to a "login" with the same name. Permissions in a database are granted to a "user" (or role), not to a "login".
So, what you see under "Permissions" are the users, not the logins. To manage the logins, including passwords etc, follow the link above.
When you connect with SSMS, be sure to double check the instance to wich you are applying your changes!
Thanks for the information. I
Thanks for the information.
I do have a login and a user setup for the "drupal_db" database, and they both have the same name as you stated.
In SSMS, when I look under the "Security" folder for the "drupal_db" database, I see the the "drupal" account under "Logins". When I view its properties, I see the password that I set.
I then go into the database"drupal_db" database itself to see the list of "Users" under its own Security folder, and I see the "drupal" user listed there as well.
I've also installed Acquia Drupal through Web Matrix, and when I try to set up a "New Connection" to the database, I'm getting the error listed below. Do you suggest uninstalling the Acquia version of Drupal, and going with something else?
---ERROR LOG BEGINS---
Login failed for user 'drupal'. The user is not associated with a trusted SQL Server connection.
ResourceName:
Microsoft.WebMatrix.Server.WebManagementServiceException: Login failed for user 'drupal'. The user is not associated with a trusted SQL Server connection. ---> System.Data.SqlClient.SqlException: Login failed for user 'drupal'. The user is not associated with a trusted SQL Server connection.
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, TimeoutTimer timeout)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, TimeoutTimer timeout, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at Microsoft.WebMatrix.DatabaseManager.SqlDatabase.SqlDatabaseProvider.TestConnection(String connectionString)
at Microsoft.WebMatrix.DatabaseManager.IisDbManagerModuleService.TestConnection(Object databaseConnectionObject, String configPathState)
--- End of inner exception stack trace ---
at Microsoft.WebMatrix.Client.SharedMemoryChannel.InvokeInternal(String serviceName, String methodName, Object[] parameters)
at Microsoft.WebMatrix.Client.SharedMemoryChannel.Invoke(String serviceName, String methodName, Object[] parameters)
at Microsoft.WebMatrix.Client.ModuleServiceProxy.Invoke(String methodName, Object[] parameters)
at Microsoft.WebMatrix.DatabaseManager.Client.IisDbManagerModuleProxy.TestConnection(DatabaseConnection connectionObject, String configPath)
at Microsoft.WebMatrix.DatabaseManager.Client.ClientConnection.Test(ManagementConfigurationPath configPath)
at Microsoft.WebMatrix.DatabaseManager.Client.DatabaseHierarchyInfo.EnsureLoaded()
---ERROR LOG ENDS---
SQL Server Access
This is due to the same authentication error that you are experiencing in the command line.
Are you able to connect to your instance via SSMS with your "drupal" login?
In the login dialog box you can choose to use integrated authentication (i.e. your Windows user) or provide a SQL server user and password.
I'm not sure if I can connect
I'm not sure if I can connect to my instance using the drupal login, because of the errors I'm getting when attempting to do so in the command line and within Acquia Drupal.
I did select SQL Server Authentication instead of Windows Authentication, and that's the same password that I've been entering into the command line and Web Matrix.
I've decided to go another route and attempt to download the Commerce Guys version instead of the Acquia Drupal version. The install downloading right now, which is taking a while so far. Hopefully the install will start soon, and I can see if that will work instead. Please let me know if anything I've said seems to be going in the wrong direction, as I am a newbie at all of this. Thanks.
SQL Server Access
Access to SQL server is independent from the version of Acquia that you install.
You should be able to connect to the instance via SSMS or SQLCMD before attempting the setup, but of course there's nothing wrong in trying alternative options. :-)
Could my instance of SQL
Could my instance of SQL Server 2008 R2 Express Edition that's installed locally be different than the .\SQLEXPRESS that it's trying to connect to?
With the Commerce Guys install, I'm having the same problem connecting: it downloaded properly after selecting "Use existing database", but during the install, I got the error: "The database 'Drupal' could not be created. The login failed for 'drupal'".
When I try to create a new database with different login, user, and database information, it gives me that error: "Login failed for user 'drupal_sa'". How can a login fail for a user that it's supposed to create?
have you taken a look at your
have you taken a look at your webmatrix install log? it may be looking at your sql server 2005 client installation still...
SQL Server Access
As I wrote you, I think it's a connectivity issue to the instance, which has nothing to do with the setup method you can choose.
Can you please run the command line script I posted to create a login/db/user, using SQLCMD .S .\INSTANCENAME -E.
Please post any error that it gives you, and be sure to choose the SQL 2008 R2 instance you installed!
That's the only way I have to be user that everything is in place :-)
Ok, I was able to do the
Ok, I was able to do the command line code you included successfully:)
For the database and login sections, it said that they already existed. When I then ran the two stored procedures, they went through with success. What I saw on the command line is below.
I then tried to redo the Commerce Guys install with "use existing database", and received the same error: "This product did not install successfully: The database 'drupal_db' could not be created. Login failed for user 'drupal'."
This is what I see at each command line step:
sqlcmd -S.\MYINSTANCE -E
GO
Result: 1>
create login drupal with password = 'MYPASSWORD'
GO
Result:
create login drupal with password = 'MYPASSWORD'
Msg 15025, Level 16, State 1, Server MYCOMPUTERNAME, Line 1
The server principal 'drupal' already exists.
create database drupal_db
GO
Result: Database 'drupal_db' already exists. Choose a different database name.
create user drupal for login drupal
GO
Result: create user drupal for login drupal
exec sp_addrolemember 'db_owner', 'drupal'
GO
Result: exec sp_addrolemember 'db_owner', 'drupal'
exec sp_defaultdb 'drupal', 'drupal_db'
GO
Result: exec sp_defaultdb 'drupal', 'drupal_db'
SQL Access
as you can see in the script, there are errors complaining that the login and db already exist.
Can you please drop the existing login and db or create new ones by changing the login name, user name and db name as stated in the original post related to the script?
I did everything from the
I did everything from the previous post in the command prompt, and it went through without a problem.
But...
1) When I try this:
sqlcmd -S.\MYINSTANCE -U drupal
I get the error after entering the password I created: "Cannot open user default database. Login failed. Login failed for user 'drupal'.
2) When I enter this:
sqlcmd -S.\MYINSTANCE -E
CREATE LOGIN drupal with password = 'MYPASSWORD'
GO
I get this: The server principal 'drupal' already exists.
It's saying that everything exists now, but I cannot see them in SSMS even after rebooting, and it will not let me log in to check if the everything's set up as you said in your previous post (like below):
write:
USE yourdrupaldb
GO
Check for error messages.
Now let's do an empiric test to see if you have the proper permissions:
CREATE TABLE quicktest (id int)
GO
DROP TABLE quicktest
GO
to exit:
QUIT
SQL Server Access
In this case I'd suspect that you connect to the wrong instance in SSMS.
In SSMS, from the menu choose "File -> Connect Object Explorer...", and connect with the same parameters that you are using in the command line test.
You should fid your login under "Security" -> "Logins" in the Object Explorer on the left.
I did that but could not find
I did that but could not find the login, user, or database on either of the two instances that were listed. When I installed SQL Server, I first installed the version without tools, uninstalled it, and the installed the version without tools.
So I went ahead and uninstalled everything associated with SQL Server 2008 R2 Express Edition, and I'm in the process of re-installing it. When it asked me for an instance name, I entered "SQLEXPRESS". It said that "SQLEXPRESS" was already being used, so that could be the problem. I'm naming the new instance "SQLEXPRESS2008", and I'm installing it now.
One I've done that, I will retry setting up the properties of the Drupal database using your command line code, and go from there. I'll let you know of the result. Thanks for all of your help.
HALLELUJAH!!! I actually sang
HALLELUJAH!!! I actually sang it when I saw the "Congratulations" screen:)
1) I was able to create the login, database, and user without a problem using your previous command line prompts...yea!
2) I then was able to succesfully create and drop a test table using your previous command line prompts...another yea!!
3) And I was finally able to install the Commerce Guys version of Drupal 7...triple yea!!!
So the complete uninstall and reinstall was the way to go. Now, I've started Drupal within Web Matrix, but when I click on the http://localhost:12345 link, I get this error message:
The website encountered an unexpected error. Please try again later.
Error message
PDOException: SQLSTATE[42S02]: [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name 'semaphore'.: SELECT expire, value FROM {semaphore} WHERE name = :name; Array ( [:name] => variable_init ) in lock_may_be_available() (line 164 of C:\Users\MYUSERNAME\Documents\My Web Sites\Drupal 7.2 Developer release\includes\lock.inc).
Uncaught exception thrown in shutdown function.
PDOException: SQLSTATE[42S02]: [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name 'semaphore'.: DELETE FROM {semaphore} WHERE ([value] = :db_condition_placeholder_0) ; Array ( [:db_condition_placeholder_0] => 7323097334f480b3a7c0275.29457442 ) in lock_release_all() (line 266 of C:\Users\MYUSERNAME\Documents\My Web Sites\Drupal 7.2 Developer release\includes\lock.inc).
Do you know what this means?
*****************EDIT********************
I just found this Drupal forums article on the subject, and it solved the problem: http://drupal.org/node/1069488
What a great end of the week!
alexpilotti...I greatly appreciate your help, and I hope that Drupal 7/SQLServer/IIS7 works out for me. Thanks for all of your help!!!
SQL Server Access
Glad to hear that you got through the SQL issues you had, congratulations! :-)
The table/view "semaphore" that Drupal tries to query is not existing or accessible. Looks like you didn't run install.php. If you look in this thread, somebody had your same error. :-)
Install is available in the root of your Drupal installation, e.g.: http://localhost:12345/install.php
Let me know if this solves it!
Great!
Great, I'm glad you solved it! :-)
could be a database grants issue
The D7 database should contain 70+ tables after installation. The semaphore table should be present. You might be able to install with insufficient grants, but you wil run into problems later. Check this documentation for proper install: https://drupal.org/documentation/install/create-database.
The error report mentions "release\includes\lock.inc"
Here's more info about lock.inc https://api.drupal.org/api/drupal/includes%21lock.inc/7
I'm no programmer, but it seems to be related to the 'lock table' database grant permission.
As for http://drupal.org/node/1069488, I fail to see how changing the http port would solve this issue
Hi alexpilotti, Thanks for
Hi alexpilotti,
Thanks for replying.
No, I hadn't considered a manual install, but if that's a way for me to accomplish what I need, that would be great.
I have IIS7 installed locally, and I'd like to test Drupal 7 locally. Now I'm working on installing SQL Server 2008 R2 Express Edition locally, and then I'll follow the steps you've provided. I'll let you know how it works out for me. Thanks for the direction.
sql user in db owners for drupal
My thought is to be sure to create a global sql user account for drupal to use and add a user to the db that is connected to that user, and grant membership in the db owners group. That's been our formula for MS Sql apps in general. Don't use the sa account, that's future trouble waiting to happen. Keep us posted!
Thanks for that info verta.
Thanks for that info verta. Good points. I'll keep that in mind when setting things up.
Multiple web servers (IIS 7)
This is great! Now, where can I find information on using Drupal for a site which will run on multiple Windows web servers (IIS 7)?
I've been looking all over the internet but not finding much useful information. How is this done?
Thanks!
D
Multiple Web Servers (IIS 7)
Hi D,
We did a pre conference training at DrupalCon in Denver about creating Drupal Web Farms on IIS 7. You can find here a great blog post by Brian Swan about it including also a link to the slides I prepared for the training.
Let me know if you have any question!
Alessandro Pilotti
MVP ASP.Net / IIS
Thank you VERY much
Thank you VERY much Alessandro! I will study this, looks like exactly what I've been searching for! I really appreciate your help here!
Thanks again,
D
error message
I had work work drupal before in 2010. stopped for a long spell and loss all my touches. I've just started and realized nothing seems to work properly for me now until I google a problem and solve which seems like the way forward. I just configure a site locally but I still now get an error message:
Error
Error message
PDOException: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'mcc.blocked_ips' doesn't exist: SELECT 1 FROM {blocked_ips} WHERE ip = :ip; Array ( [:ip] => ::1 ) in drupal_is_denied() (line 1910 of /Applications/MAMP/htdocs/mcc/includes/bootstrap.inc).
The website encountered an unexpected error. Please try again later.
Is anyone there that can help me out?
Thanks
We are wanting to implement a
We are wanting to implement a Drupal 7 solution for a client in a Windows environment. We need the database to be able to connect to an application running on their server. What has been your experience with this solution? Would you recommend it or recommend looking elsewhere?
Thanks