MS SQL Issues

dman

dman

Perch
Hello,

For the last couple of days I have had issues working with MS SQL via SMS. I am trying to create a table either on the server or locally using SMS 2005. When I try "Script Database As" on MSSQL9 I get the error below. I have changed my the user and db to user_db.
______________________________________________________________

TITLE: Microsoft SQL Server Management Studio
------------------------------

Script failed for Database 'user_db'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?Prod...eptionText&EvtID=Script+Database&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

User 'user_db' does not have permission to run DBCC SHOWFILESTATS for database 'user_db'. (Microsoft SQL Server, Error: 7983)

For help, click: http://go.microsoft.com/fwlink?Prod...39&EvtSrc=MSSQLServer&EvtID=7983&LinkId=20476


When I try to create a table or "Generate Scripts" I get this error:
_________________________________________________________________

TITLE: Microsoft SQL Server
------------------------------

This wizard will close because it encountered the following error:

For help, click: http://go.microsoft.com/fwlink?Prod...kErrorSR&EvtID=UncaughtException&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

Failed to connect to server mssql9.xxx.com. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Cannot open user default database. Login failed.
Login failed for user 'user_db'. (Microsoft SQL Server, Error: 4064)

For help, click: http://go.microsoft.com/fwlink?Prod...er&EvtSrc=MSSQLServer&EvtID=4064&LinkId=20476

I've put in a support ticket but they have not resolved it yet. Any suggestions on how I might fix it??( I do sanitize any input but it is possible I missed something and this related to the SQL Injection Attack Stephen posted about.
http://support.jodohost.com/showthread.php?t=12246

Any help is appreciated. Thanks!
 
Another update, I can import and export data, open tables and delete records, and generally do anything other than create tables, Script Database As, and Generate Scripts. My web sites are still collecting and displaying data from MS SQL as well. IS this a Schema issue? What would cause this? I've been going back and forth with support all day but still do not have a resolution. Thanks!
 
DBCC SHOWFILESTATS is a permissions related error, as you don't have this level of permissions.

We didn't make any changes SQL side to prevent SQL injections, that is done on ASP code side, we certainly didn't want to move to a read only mode for SQL that would be a disaster.
 
Hey Stepehn,

Thanks for the quick reply. I swear I was able to Script Database As last week when I was trying to duplicate a database. Should I not be able to use Script DB As?

I Understand there isn't anything that can be done server side for SQL injection attacks, but I'm really confused why my DB admin login now has limited functionality. Any ideas why I'm getting these errors? Shouldn't I be able to create a table via SMS by right clicking then choosing New Table?

I've tried scripting a new table locally in SMS 2005 and then executing it on MSSQL9 but I get the following error:

Msg 170, Level 15, State 1, Line 8
Line 8: Incorrect syntax near '('.

Below is the script:

USE [user_db]
GO
/****** Object: Table [admin_user].[tblmain] Script Date: 05/15/2008 19:26:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [admin_user].[tblmain](
[tblmainID] [int] IDENTITY(1,1) NOT NULL,
[BVNumber] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BVName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_tblmain] PRIMARY KEY CLUSTERED
(
[tblmainID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
 
Ok, not sure if Jodo has made any changes to my login but I can script to after removing the following from the above script:

CONSTRAINT [PK_tblmain] PRIMARY KEY CLUSTERED
(
[tblmainID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

Stephen,

I would assume that I am the DBO or at least admin. I thought DBO was reserved for Jodo only. Looking at my security for this DB I see my SQL admin login and DBO.

On another note, I do see all the DB's when I log in although I cannot open any, I have to scroll thru them all. Is this the way it should be?

Also, I tried creating a MS SQL user to use in the web site DB connections but it appears only the admin login can be used with connections from the web site. Is this correct? Thanks!
 
if you are using SMS 2005 then yes you can see all the DBs, on older Enterprise Manager you can't.

DBO can be granted on a per database basis without a problem just ask for it :)
 
Thanks Stephen I may ask for this but not unless I can use a MS SQL user for connection strings in the code. Any idea why the MS SQL users created in Hsphere will not work in connections strings in code?
 
I am not sure I am understanding, if you are not dbo the tablename will auto prefix user.table as DBO you can specify user.table or use dbo.table
 
Hey Stephen,

Thanks for the reply. Forgive my lack of understanding. I am still a bit green when it comes to MS SQL server.

Let me see if I can clarify further. My understanding is that it is better not to use the Hsphere SQL Database login for ADO connections. I have tried using a Hsphere created Database User to connect via ADO but I get permission errors.

Are you saying the reason the Hsphere Database User account I created for this DB does not work with ADO is because the table names are prefixed with Hsphere's Database Login? Because of the Database Login prefix Hsphere's Database Users can't access the table. Is this correct?

What you are suggesting is that I ask for DBO access to my DB. Then I will have access to change the properties and permissions, including the table prefix? If I change the prefix to dbo.table will both Hsphere's Database login and Hsphere's Database user be able to connect via ADO? How does the table prefix effect permission on the table or database with Hspehere created logins and users?

I think I'm getting more confused. ?( Can anybody straighten me out? Thanks!
 
If you aren't using an hpshere created user what user are you using? Only hsphere created users can connect at all. All the users can use ADO without a problem.
 
I think you need to remove the ON [PRIMARY] stuff.

I had to create scripts for a DotNetNuke module recently. I did this by doing a SCRIPT TABLE AS on my local db and then making some modifications to work on JodoHost. I'm going by memory right now but I think the main thing I had to do was remove the ON [PRIMARY] and let the server choose an appropriate default.

I think the ON PRIMARY defines what data file group the table is created in. It seems reasonable that I would not be able to control that on Jodo's servers.
 
Hey Stephen,

Thanks for the reply...

If you aren't using an hpshere created user what user are you using? Only hsphere created users can connect at all.
Click to expand...

Right...

All the users can use ADO without a problem.
Click to expand...

This has not been my experience. I created a user in Hsphere and received permission issues when using ADO. Maybe I am not using the right connections string? Here is the error:

Microsoft OLE DB Provider for SQL Server error '80040e4d'

Login failed for user 'database_user.

Here is the connection string. It works with the database login but it returns the above error with a database user. When you add database user in Hsphere it does not ask for a password. Is it working under the database login schema? Do I use the database login password? I'm sure I am missing something here...
----------------------------------------------------
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "Provider=sqloledb;Data Source=mssqlx.xxx.com;" & _
"Initial Catalog=account_db;User Id=db_user;Password=password;"

Also, I just got a response back for my support ticket and they have granted DBO permissions. I can now use Generate Scripts and Script Database As!!!

Hey tetranz -

Thanks for the reply. I was finally able to script a new table. I scripted it from a local table and had parse errors, Msg 170: etc....

When I removed the Constraint functions and the WITH statement, it worked. Not sure if I needed these. I had to remove the following script in BOLD to make it work:

USE [db]
GO
/****** Object: Table [db_login].[tablename] Script Date: 05/16/2008 19:05:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [db_login].[tablename](
[column1] [int] IDENTITY(1,1) NOT NULL,
[column2] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[column3] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_tablename] PRIMARY KEY CLUSTERED
(
[primary_key] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

The last ON [PRIMARY] at the end that didn't seem to cause a problem but I'll keep that in mind as I script more tables. Thanks!
 
I haven't seen anyone in the past having similar issues with ADODB with a similar connection string, that is quite odd unless indeed there is some error in the user/password which from your posts here and experience, seems unlikely.
 
Hey Stephen,

Thanks for the vote of confidence! I don't believe this is an issue with the username and password either. I'm assuming my issue is with how I am setting up the user accounts.

What I want is a DB login and user that does not have all the permissions of the main DB login/admin to remove elevated permissions from the web connection if the web site is attacked or has a vulnerability. My thought is that this will reduce damages and increase security. Is this right?

Maybe you or someone else can correct my confusion...

I have a database: accountname_testDB
This DB has a login: accountname_admin
This DB has a user: accountname_user

Using the following connection string does not work. Should I be able to use the DB user (accountname_user) with the DB login (accountname_admin) password?

objConn.Open "Provider=sqloledb;Data Source=mssqlx.xxx.com;" & _
"Initial Catalog=accountname_testDB;User Id=accountname_user;Password=accountname_admin_password;"
​
I think my confusion is with the login associated with user and the difference between a login and a DB user in Hsphere. So now I've set up the following:

DB Login1: accountname_admin - The Database login for all DBs.
DB Login2: accountname_webadmin - A separate login
DB User1: accountname_webuser - This is the user for all DBs. It uses accountname_webadmin as the MSSQL login. IS this the schema?

I can connect using the following string using the DB Login2 from above: accountname_webadmin and password.

objConn.Open "Provider=sqloledb;Data Source=mssqlx.xxx.com;" & _
"Initial Catalog=accountname_testDB;User Id=accountname_webadmin;Password=accountname_webadmin_password;"
​
Assuming the default permissions set by Hsphere's MS SQL interface, is this more secure than using the actual DB login for the database? Or, do I need to change the permissions using SMS, if possible, for the webadmin or webuser?

Finally, is this overkill and not needed?
 
You must log in or register to reply here.
Back
Top