How can I connect to a remote sql server using windows authentication?

It is simple

1- I open the Sql Server 2005 Management Studio

2- I select Windows Authentication from the drop down.

3- I cannot write the user name and password, it chooses the default once, the one I am logged in with!

But I am in a virtual machine outside the domain controller, I can access shares on machines that are on the domain controller, thanks to the file sharing of windows, but I cannot login to sql server, thanks to a meaningless restriction on that dialog :-)

Now, how can I still use the Windows Authentication and login, how can I avoid the sql server authentication?

If you are outside domain, then you should use SQL Authentication.|||

Ok, then why the text boxes for the user name and password are still there if I choose the Windows authentication, and the text boxes have the user name already filled in, and all are disabled.

What is the point of having those there? In my case, I was trying to find a way to enable them from the settings; I guess just a false hope.

And why cannot I use the windows authentication? NTFS does allow me to do it and access the file system from outside the domain using windows authentication against the domain, what does make sql server more special?

|||

The reason for the textbox is just to let you know which Windows account is being used to connect to SQL Server using Windows authentication. To access SQL Server using SQL authentication, click the Authentication drop-down to see the SQL Server Authentication option. You'll see the User name and Password textboxes enabled.

If you want to use Windows authentication, the easiest way is to join your SQL Server to your domain.

|||

Thank you for the help, but I know how to use the SQL Server authentication, and the SQL Server is the development server and it is on the domain.

My virtual machine is the development machine, it is a virtual machine and it cannot join the domain, it must stay as it is, the real machine is on the domain, but the virtual machine that I am trying to use is not.

From the virtual machine I can do lots of things, including accessing the file system and the intranet sites on the domain, using the domain authentication box, or cached credentials, but I cannot do that with the SQL Server.

|||

if you want an nt authentication

then you must promote your virtual machine to a domain controller

How can I check if SQL Server Management Studio is installed?

How can I check if SQL Server Management Studio is installed?

[Start] \ [All Program] \ [Microsoft SQL Server 2005] \ [SQL Server Management Studio]

It is either there or not.

The default install does NOT include the client tools. If if is not listed, then using the install media, run Setup again, and select the Client Tools.

|||

Sorry.

How can I check programmatically if SQL Server Management Studio is installed?

|||

Either look in this location

"{installLocation}\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\sqlwb.exe"

or check this Registry entry using xp_regread

HKEY_LOCAL_MACHINE\SOFTWARE\Classes\Applications\sqlwb.exe

sql

How can I change the default Save-As/Save directory

I am new to sql sever management studio express, but a long time query analyzer user. This is a very basic question.

I want to change the default directory in sql server management studio express so that when I go to save a query, it is already pointed to the correct one. Where do I change that?

Thanks,

Nanci

Goto [Tools], [Options], [Query Results]

There you will be able to change the default query results storage location.

|||

I have changed that setting, but it only works for the results of the query, not saving the query itself. Any other suggestions?

Nanci

sql

How can i change collation of userdefined datatype?

Hi everyone?

How can i change collation of userdefined datatype?
When i try to change it Management Studio Query

varcharUzun = varchar(100) --> userdefined data type....

[code]ALTER TABLE tbxxxx ALTER COLUMN sDefinition varcharUzun COLLATE Turkish_CI_AS NOT NULL [/code]

when i run the query it says.
[code]
Msg 452, Level 16, State 1, Line 2
COLLATE clause cannot be used on user-defined data types.
[/code]

i also cannot ALTER the userdefinedtypes collation itself. And i canyt chance the collation if the column is PrimaryKey
Is there any other to change the collation without dropping and re-creating the objects.

Thanks.

Hi Cem,

I don't htink you can set collation for a column that is using USER DEFINED DATATYPE.

If you do need to use the collation, just use the system datatype varchar(100), instead of user-defined, on that column.

regards

Jag

|||

Thanks Jag

but i have to use user defined datatypes.

No way to change the COLLATION of TYPES..... i couldnt find any ALTER TYPE xxxx COLLATE = xxx :(

And also how can i change the collation of PrimaryKey (varchar(10))....

2 Main problems:(

cemuney

|||

Hi Cem,

I dont think you can change the collation for user defined data type.

To change collation for a particular column try the following:

ALTER TABLE <tableName>

ALTER COLUMN <columnName> VARCHAR(10) COLLATE <collationName>

Let me know how you got on.

regards

Jag

|||

Hi Jag

i understant that UDDType do not have collations itself.

it is coming from the DATABASE's default collation.

When i change the COLLATION of database, also UDDType's collation also changes.

thanks anyway.

How can i change collation of userdefined datatype?

Hi everyone?

How can i change collation of userdefined datatype?
When i try to change it Management Studio Query

varcharUzun = varchar(100) --> userdefined data type....

[code]ALTER TABLE tbxxxx ALTER COLUMN sDefinition varcharUzun COLLATE Turkish_CI_AS NOT NULL [/code]

when i run the query it says.
[code]
Msg 452, Level 16, State 1, Line 2
COLLATE clause cannot be used on user-defined data types.
[/code]

i also cannot ALTER the userdefinedtypes collation itself. And i canyt chance the collation if the column is PrimaryKey
Is there any other to change the collation without dropping and re-creating the objects.

Thanks.

Hi Cem,

I don't htink you can set collation for a column that is using USER DEFINED DATATYPE.

If you do need to use the collation, just use the system datatype varchar(100), instead of user-defined, on that column.

regards

Jag

|||

Thanks Jag

but i have to use user defined datatypes.

No way to change the COLLATION of TYPES..... i couldnt find any ALTER TYPE xxxx COLLATE = xxx :(

And also how can i change the collation of PrimaryKey (varchar(10))....

2 Main problems:(

cemuney

|||

Hi Cem,

I dont think you can change the collation for user defined data type.

To change collation for a particular column try the following:

ALTER TABLE <tableName>

ALTER COLUMN <columnName> VARCHAR(10) COLLATE <collationName>

Let me know how you got on.

regards

Jag

|||

Hi Jag

i understant that UDDType do not have collations itself.

it is coming from the DATABASE's default collation.

When i change the COLLATION of database, also UDDType's collation also changes.

thanks anyway.

sql

How Can I call SQL Server SMO Objects in VC6 or VB6

How Can I call SQL Server SMO Objects in VC6 or VB6

Hi

I want to use SQL SMO (SQL Management Object .Net Library) in VC 6.0 and VB.6.0

I don’t want to use SQL DMO (COM component)

I tried to make COM callable wrapper (CCW) for SMO using the following command line execution.

RegAsm.exe /tlb:MicrosoftSqlServerSmoTest.tlb Microsoft.SqlServer.Smo.dll

After executing the command line RegAsm.exe, I got the following output.

Microsoft (R) .NET Framework Assembly Registration Utility 2.0.50727.42

Copyright (C) Microsoft Corporation 1998-2004. All rights reserved.

Types registered successfully

Type library exporter warning processing 'Microsoft.SqlServer.Management.Smo.Bac

kupDeviceList, Microsoft.SqlServer.Smo'. Warning: Type library exporter encounte

red a type that derives from a generic class and is not marked as [ClassInterfac

e(ClassInterfaceType.None)]. Class interfaces cannot be exposed for such types.

Consider marking the type with [ClassInterface(ClassInterfaceType.None)] and exp

osing an explicit interface as the default interface to COM using the ComDefault

Interface attribute.

Assembly exported to 'C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\Mi

crosoftSqlServerSmoTest.tlb', and the type library was registered successfully

In My VB6 Application I have included the Refrence Microsoft_SqlServer_Smo

Then I tried to call Server Object with parameter shown below.

Dim objServer As Server

Set objServer = new Server(Server_Name)

But compile error occurs in the line Set objServer = new Server(Server_Name)

I have the following queries.

1. How can I access SMO objects in VC 6 or VB6 applications using any method?

2. How can I solve the compile error?

3. How can I avoid the Warning when calling the callable wrapper (CCW)?

4. Is it possible to use parameterized constructors in .net components?

5. Is it necessary that the GUIDs to all .NET objects so that the appropriate COM required registry entries can be created?

For example :

[Guid("2979DBB0-3371-4841-9B94-B79EAE28B503")]

public class TestInteropSample

{

}

Kindly reply me ,

Thanking you,

Kuttappan.

Hello,

I'm having the same problem. Did you ever find a solution?

thanks,

kelly

|||

SMO is only supported in VB/C#.Net 2005. It requires the .Net 2.0 Framework, which isn't available in VB/VC 6.

How Can I call SQL Server SMO Objects in VC6 or VB6

How Can I call SQL Server SMO Objects in VC6 or VB6

Hi

I want to use SQL SMO (SQL Management Object .Net Library) in VC 6.0 and VB.6.0

I don’t want to use SQL DMO (COM component)

I tried to make COM callable wrapper (CCW) for SMO using the following command line execution.

RegAsm.exe /tlb:MicrosoftSqlServerSmoTest.tlb Microsoft.SqlServer.Smo.dll

After executing the command line RegAsm.exe, I got the following output.

Microsoft (R) .NET Framework Assembly Registration Utility 2.0.50727.42

Copyright (C) Microsoft Corporation 1998-2004. All rights reserved.

Types registered successfully

Type library exporter warning processing 'Microsoft.SqlServer.Management.Smo.Bac

kupDeviceList, Microsoft.SqlServer.Smo'. Warning: Type library exporter encounte

red a type that derives from a generic class and is not marked as [ClassInterfac

e(ClassInterfaceType.None)]. Class interfaces cannot be exposed for such types.

Consider marking the type with [ClassInterface(ClassInterfaceType.None)] and exp

osing an explicit interface as the default interface to COM using the ComDefault

Interface attribute.

Assembly exported to 'C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\Mi

crosoftSqlServerSmoTest.tlb', and the type library was registered successfully

In My VB6 Application I have included the Refrence Microsoft_SqlServer_Smo

Then I tried to call Server Object with parameter shown below.

Dim objServer As Server

Set objServer = new Server(Server_Name)

But compile error occurs in the line Set objServer = new Server(Server_Name)

I have the following queries.

1. How can I access SMO objects in VC 6 or VB6 applications using any method?

2. How can I solve the compile error?

3. How can I avoid the Warning when calling the callable wrapper (CCW)?

4. Is it possible to use parameterized constructors in .net components?

5. Is it necessary that the GUIDs to all .NET objects so that the appropriate COM required registry entries can be created?

For example :

[Guid("2979DBB0-3371-4841-9B94-B79EAE28B503")]

public class TestInteropSample

{

}

Kindly reply me ,

Thanking you,

Kuttappan.

Hello,

I'm having the same problem. Did you ever find a solution?

thanks,

kelly

|||

SMO is only supported in VB/C#.Net 2005. It requires the .Net 2.0 Framework, which isn't available in VB/VC 6.

How Can I call SQL Server SMO Objects in VC6 or VB6

How Can I call SQL Server SMO Objects in VC6 or VB6

Hi

I want to use SQL SMO (SQL Management Object .Net Library) in VC 6.0 and VB.6.0

I don’t want to use SQL DMO (COM component)

I tried to make COM callable wrapper (CCW) for SMO using the following command line execution.

RegAsm.exe /tlb:MicrosoftSqlServerSmoTest.tlb Microsoft.SqlServer.Smo.dll

After executing the command line RegAsm.exe, I got the following output.

Microsoft (R) .NET Framework Assembly Registration Utility 2.0.50727.42

Copyright (C) Microsoft Corporation 1998-2004. All rights reserved.

Types registered successfully

Type library exporter warning processing 'Microsoft.SqlServer.Management.Smo.Bac

kupDeviceList, Microsoft.SqlServer.Smo'. Warning: Type library exporter encounte

red a type that derives from a generic class and is not marked as [ClassInterfac

e(ClassInterfaceType.None)]. Class interfaces cannot be exposed for such types.

Consider marking the type with [ClassInterface(ClassInterfaceType.None)] and exp

osing an explicit interface as the default interface to COM using the ComDefault

Interface attribute.

Assembly exported to 'C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\Mi

crosoftSqlServerSmoTest.tlb', and the type library was registered successfully

In My VB6 Application I have included the Refrence Microsoft_SqlServer_Smo

Then I tried to call Server Object with parameter shown below.

Dim objServer As Server

Set objServer = new Server(Server_Name)

But compile error occurs in the line Set objServer = new Server(Server_Name)

I have the following queries.

1. How can I access SMO objects in VC 6 or VB6 applications using any method?

2. How can I solve the compile error?

3. How can I avoid the Warning when calling the callable wrapper (CCW)?

4. Is it possible to use parameterized constructors in .net components?

5. Is it necessary that the GUIDs to all .NET objects so that the appropriate COM required registry entries can be created?

For example :

[Guid("2979DBB0-3371-4841-9B94-B79EAE28B503")]

public class TestInteropSample

{

}

Kindly reply me ,

Thanking you,

Kuttappan.

Hello,

I'm having the same problem. Did you ever find a solution?

thanks,

kelly

|||

SMO is only supported in VB/C#.Net 2005. It requires the .Net 2.0 Framework, which isn't available in VB/VC 6.

How can I attach AdventureWorks Database to SQL Server Management Studio Express?

Hi all,

I just downloaded AdventureWorks_Data.mdf, AdventureWorks_Data.ldf, AdventureWorksDW_Data.mdf, and AdventureWorksDW_Data.LDF files to the C:\Program Files\Microsoft SQL Server\MSSQL.1|\MSSQL\Data folder of my Windows XP Pro PC. I tried to attach the AdventureWorks database in my SQL Server Management Studio Express in the following way: I clicked on SQL Server 2005 Express=>SQL Server Management Studio Express=>Right clicked on Databases=> clicked on "Attach..."=> "Attach Databese" showed up. But I can not type in anything under the MDF File Location, Database Name, Attach As, Owner, Status, Message!!!? What is wrong in my way of doing the attachment of AdventureWorks database (described above)? Please help and advise.

Thanks,

Scott Chang

P. S. I do not have Microsoft Visual Studio 2005. Walkthrough: Install the AdventureWorks Database (http://msdn2.microsoft/en-us/library/ms31035.aspx) : To confirm your SQL Server data directory (Start=>All Programs, Microsoft Visual Studio 2005, Visual Studio Tools, Visual Studio 2005 Command Prompt) steps 1 thru 4 can not be applied to my SQL Server Management Studio Express. I read an SQL Express post dated 19 April 2006 and learned the above-mentioned way to attach the AdventureWorks database in the SQL Server Management Studio Express. But that way does not work for me.

Hi Scott,

You can't type in the name of the database, you need to click the Add button, which will open a browse window onto the Data folder, where you can select the MDF file you desire. Once added, you can click OK to complete the attach.

Mike

|||

Why are some folders unbrowsable in SSMSE? I have an .mdf file located under c:\documents and settings\myusername\blah blah blah.... and when I try to browse to my mdf file I get no further than c:\mydocuments\myusername. As if there are no subfolders. Is this a folder rights issue?

|||

Yes, it's a folder rights issue.

SQL Express runs as the Network Service account, which doesn't have permissions to any of the User profile directories such as My Documents. If you want to have your database always attached to SQL Express and available to everyone, then you should put the file in the SQL Data directory and attach it from there. If you want the database to be reserved for just a single user, then I would suggest you explore User Instances, which is a special way to run SQL Express under specific users accounts so that it does have permission to the User profile directories.

User Instances are used by default when you insert a database into a Visual Studio project. You can find more information here.

Mike

How can I attach AdventureWorks Database to SQL Server Management Studio Express?

Hi all,

I just downloaded AdventureWorks_Data.mdf, AdventureWorks_Data.ldf, AdventureWorksDW_Data.mdf, and AdventureWorksDW_Data.LDF files to the C:\Program Files\Microsoft SQL Server\MSSQL.1|\MSSQL\Data folder of my Windows XP Pro PC. I tried to attach the AdventureWorks database in my SQL Server Management Studio Express in the following way: I clicked on SQL Server 2005 Express=>SQL Server Management Studio Express=>Right clicked on Databases=> clicked on "Attach..."=> "Attach Databese" showed up. But I can not type in anything under the MDF File Location, Database Name, Attach As, Owner, Status, Message!!!? What is wrong in my way of doing the attachment of AdventureWorks database (described above)? Please help and advise.

Thanks,

Scott Chang

P. S. I do not have Microsoft Visual Studio 2005. Walkthrough: Install the AdventureWorks Database (http://msdn2.microsoft/en-us/library/ms31035.aspx) : To confirm your SQL Server data directory (Start=>All Programs, Microsoft Visual Studio 2005, Visual Studio Tools, Visual Studio 2005 Command Prompt) steps 1 thru 4 can not be applied to my SQL Server Management Studio Express. I read an SQL Express post dated 19 April 2006 and learned the above-mentioned way to attach the AdventureWorks database in the SQL Server Management Studio Express. But that way does not work for me.

Hi Scott,

You can't type in the name of the database, you need to click the Add button, which will open a browse window onto the Data folder, where you can select the MDF file you desire. Once added, you can click OK to complete the attach.

Mike

|||

Why are some folders unbrowsable in SSMSE? I have an .mdf file located under c:\documents and settings\myusername\blah blah blah.... and when I try to browse to my mdf file I get no further than c:\mydocuments\myusername. As if there are no subfolders. Is this a folder rights issue?

|||

Yes, it's a folder rights issue.

SQL Express runs as the Network Service account, which doesn't have permissions to any of the User profile directories such as My Documents. If you want to have your database always attached to SQL Express and available to everyone, then you should put the file in the SQL Data directory and attach it from there. If you want the database to be reserved for just a single user, then I would suggest you explore User Instances, which is a special way to run SQL Express under specific users accounts so that it does have permission to the User profile directories.

User Instances are used by default when you insert a database into a Visual Studio project. You can find more information here.

Mike

How can I access SQL Server Compact Edition with SQL Server Management Studio Express?

Hello,

I just installed SQL Server Compact Edition, since I am considering using it instead of SQL Server Express for a local database in my application. The documentation mentioned that I could use SQL Server Management Studio Express to connect to the Compact Edition and create and manipulate databases.

To try to connect, I run Management Studio and bring up the "Connect to Server" dialog. Unfortunately, the pull-down list of "Server name"s does not include the SQL Server Compact Edition server. I do not know how to type in the server name manually, so I cannot connect.

To install Compact Edition, I downloaded it and ran "SQLServerCE31-EN.msi". This installed, and I assume registered, a number of DLLs in "C:\Program Files\Microsoft SQL Server Compact Edition\v3.1\".

Is there perhaps an additional step that I left out to complete the installation?

Might I need an upgrade to some other components? My Management Studio Version is:

- Microsoft SQL Server Management Studio Express 9.00.2047.00

I would appreciate any help you can provide.

Thank you,

WTW

The support for SQL Server 2005 Compact Edition was added in Microsoft SQL Server Management Studio Express (SSMSE). SSMSE is available for download at:

http://www.microsoft.com/downloads/details.aspx?FamilyId=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&displaylang=en

SSMSE shipped with SQL Server 2005 SP2. SQL Server 2005 SP2 information is at

http://technet.microsoft.com/en-us/sqlserver/bb426877.aspx#3

Regards

Ambrish

|||

WTW,

I've written a blog post about this, you can see it at http://arcanecode.wordpress.com/2007/04/11/create-a-sql-server-compact-edition-database-using-sql-server-2005-management-studio/ . I've also written an article that describes all the components you'll want to install to make everything work right, http://arcanecode.wordpress.com/2007/04/24/sql-server-2005-compact-edition-important-component/ .

You might also want to check the Arcane Lessons page for more SSCE tutorials.

Arcane

|||

Thanks for your reply. I uninstalled SSMSE and downloaded and installed it again. However, I still cannot access a SQL Server 2005 Compact Edition database.

When I bring up the "Connect to Server" dialog, the "Server Type" is fixed at "Database Engine", and I cannot determine how to change it. I've tried most of the menus and buttons, and I cannot find a setting for this.

I am missing some other component or configuration option?

Thanks for your help.

WTW

|||

Thanks for your very helpful reply. Thanks to your blog, I now have all of the proper components installed to make Compact Edition work properly. Your blog was the only place where I have found a complete list of all of the necessary components.

I have even purchased the E-book that you recommended, and it is very helpful.

However, I still am not able to access a Compact Edition database through SQL Server Management Studio Express. When I bring up the "Connect to Server" dialog, the "Server Type" is fixed at "Database Engine", and I cannot determine how to change it to Compact Edition or something similar.

The E-book demonstrates how to access a Compact Edition database through Management Studio. However, it shows a "Connect" menu in Management Studio that does not exist on my version. According to the E-book screen shots, the "Connect" menu should allow you to select "SQL Server Mobile", which will then bring up a "Connect to Server" dialog that shows a "Server Type" of "SQL Server Mobile". The next line allows selection of a "Database file" instead of a "Server name".

My version of Management Studio does not have the "Connect" menu. I even tried reinstalling Management Studio from the Microsoft website. However, I discovered that I already had the latest version, 9.00.2047.00.

I would appreciate any additional help you can provide.

Regards,

WTW

|||In typical microsoft style, the documentation is horrible on this. I was only able to get it to work by installing SP2 of SSMSE (of which there is no mention on the regular SSMSE download page) at:

http://www.microsoft.com/downloads/details.aspx?familyid=6053C6F8-82C8-479C-B25B-9ACA13141C9E&displaylang=en

and the SQL CE SDK found at:

http://www.microsoft.com/downloads/details.aspx?familyid=E9AA3F8D-363D-49F3-AE89-64E1D149E09B&displaylang=en

You will know you have SP2 of SSMSE installed if you version is 9.00.3042.00
|||

SP2 did the trick. Thank you so much.

Regards,

WTW

how can attack database from sqlserver management studio express?

I have created a database named s_office,now i want add file data.MDF and log.LDF into it. .How can i do it?Could you show me the script that attack file .MDF and .LDF into database.thankxhttp://msdn2.microsoft.com/en-us/library/ms179877.aspx

How best to Create database

Please how best is it to create a database with SQL SERVER Express. I use it but with the management studio

What do you mean by best? Beside creating in Management Studio, you can also create using T-SQL command that can be run in your application; or even use SQL-DMO to do this. Here is a good example for creating database from application:

http://download.microsoft.com/download/C/3/8/C3888A3E-52F8-4FE9-8E41-89150AB0302F/CreateDB.zip.exe

And for more information about SQL-DMO, you can refer to:

http://msdn2.microsoft.com/en-us/library/ms132924.aspx