OK Microsoft SQL gurus, teach me something!

[aronesz]

I am trying to install WS_FTP Server using a remote Microsoft SQL database. We have a server at Rackspace setup to be a secure FTP server, and another server at Rackspace provisioned as a Microsoft SQL server running Windows 2008 R2 running the latest and the greatest.

During installation of WS_FTP Server, I am prompted to enter in a Server\Instance, Port (1433), Database username, and Password. For Server\Instance, I just enter in the hostname of the server (i.e. SRV-SFTP). Port is already automatically filled. I created a user 'wsftp1' under SRV-SQL1 (SQL Server 10.50.2500 - COMPANY\AdminAccount)\Security\Logins in Microsoft SQL Server Management Studio. I also created a database under SRV-SQL1 (SQL Server 10.50.2500 - COMPANY\Databases called 'wsftp1'. When I created this database, I made user 'wsftp1' the Owner.

When I run through the WS_FTP Server installation (when it actually copies files, configures, writes registry keys, creates services, starts up, etcetera) I encounter a bunch of errors, identical to the ones at http://forums.ipswitch.com/Topic46458-8-1.aspx (year 2008, version 6). I am using the latest version 7.5 for WS_FTP Server.

I look in the SRV-SQL1 (SQL Server 10.50.2500 - COMPANY\Management\SQL Server Logs\Current - 4/30/2012 3:05:00 PM log file, and it has a half a dozen pairs of errors.

The first error is:

Date 4/30/2012 3:59:35 PM
Log SQL Server (Current - 4/30/2012 3:33:00 PM)

Source Logon

Message
Error: 18456, Severity: 14, State: 38.

The second error is:

Date 4/30/2012 3:59:35 PM
Log SQL Server (Current - 4/30/2012 3:33:00 PM)

Source Logon

Message
Login failed for user 'wsftp1'. Reason: Failed to open the explicitly specified database. [CLIENT: xxx.xxx.xxx.xxx]

I don't know what to do. I have spent an hour or so just doing hard Googling without finding a distinct solution (mostly because I lack Microsoft SQL experience and knowledge). Would this be PostgreSQL or MySQL, I would know what to do (because I have almost a decade of deep experience in the webhosting industry and running my own company).

EDIT: Unmentioned detail -- I also set the default database to 'wsftp1' for the user 'wsftp1'

 

[v6maro]

Can you load up sql management studio from the ftp box and login to the database? Did you allow the sql port through the firewall on the SQL server? Windows 2008 Server is not like 2003

 

[Tawnos]

Did you enter the user as wsftp1 or as SRV-SQL1\wsftp1?

 

[aronesz]

Can you load up sql management studio from the ftp box and login to the database? Did you allow the sql port through the firewall on the SQL server? Windows 2008 Server is not like 2003

The firewall is already completely disabled. The WS_FTP Server installer will not proceed if it cannot establish a connection with the SQL server, and if it is unable to authenticate -- which it was able to do both (or at least recognize an existing and legitimate username that exists in SQL).

Did you enter the user as wsftp1 or as SRV-SQL1\wsftp1?

wsftp1

Leaving work now, so I will look tomorrow more into the replies I have received in multiple threads I have created across other forums.

 

[v6maro]

The firewall is already completely disabled. The WS_FTP Server installer will not proceed if it cannot establish a connection with the SQL server, and if it is unable to authenticate -- which it was able to do both (or at least recognize an existing and legitimate username that exists in SQL).

wsftp1

Leaving work now, so I will look tomorrow more into the replies I have received in multiple threads I have created across other forums.

Ok, so this is the WS_FTP wizard that's failing to create it's DB on your SQL server? Sounds like the user you created does not have the proper permissions to create tables. I would check with WS_FTP and see what the deal is, there should be some support with them.

 

[renixinq]

Is named pipelines enabled? If just using the netbios name this is required or alternatively you can use IP or FQDN which are enabled by default (named pipelines is disabled by default). You'll find this in the SQL Server Config Manager.

And I'm also assuming you only have a single instance installed and it is a default not a named instance.

 

[aronesz]

Ah, well, we scrapped the SQL server after we discussed plans with Sharepoint, Exchange, Track-It!, and WS_FTP. Decided to scrap the 16GB 4vcore server; each thing will run on its own box. ;\ Installed WS_FTP with PostgreSQL successfully. Thanks for the suggestions guys! I'm sure these suggestions may be useful to someone else in the future (perhaps me again).

 

[mikeblas]

State 38 means the database doesn't exist, or the user trying to log in with that database as their initial catalog doesn't have rights to access that database. It means that the user does have valid credentials for the server.

 

[aronesz]

State 38 means the database doesn't exist, or the user trying to log in with that database as their initial catalog doesn't have rights to access that database. It means that the user does have valid credentials for the server.

User 'wsftp1' had 'public' and 'db_owner' roles on the database 'wsftp1'.

 

[mikeblas]

User 'wsftp1' had 'public' and 'db_owner' roles on the database 'wsftp1'.

I don't think they did; if they had, you would'nt have gotten that error and state.

Perhaps wsftp was in the db_owner role for that database, but it was trying to connect with a different database as its inital catalog (like master). Or, it was also in a deny role, or it had been denied connect privs explicitly.

 

[aronesz]

I don't think they did; if they had, you would'nt have gotten that error and state.

Perhaps wsftp was in the db_owner role for that database, but it was trying to connect with a different database as its inital catalog (like master). Or, it was also in a deny role, or it had been denied connect privs explicitly.

Doesn't explain why they were checked. I never checkboxed db_owner myself; it was already checkmarked. That's one of the things I didn't understand because after looking up what db_owner does, the errors in the SQL logs didn't correlate.

 

[mikeblas]

Nothing will explain *why* they were checked; someone else executed the code that causes the role to be checked, and you'll have to ask them *why* they set that right. I thought you were trying to figure out why the software you were using couldn't log in with that user.

I suggest scenarios where the presence of the role membership is consistent with the symptoms you've explained to us. The error means what it means; it's pretty well-documented. You're running a version of SQL Server that doesn't include the name of the database in the message, and that leaves out one clue--which database was being attempted? A SQL Profiler might have given you a bit more insight into the issue, as may have trying to log in with those credentials interactively, yourself.